Thread: Strange phenomena when use function

Hi, there
I developed a function to return a string. However, if I return the function value after the line below, it always comes out as “#Value!” in the location I am calling it. Any suggestions?

Function FindString() as String
Selection.Find(What:=LatNow, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
FindString="MyString"
END Function

Thanks in advance.

Regards,
Lily

If you are trying to use that function from the worksheet it will fail as you have code to activate the cell containing searched for text, which is not allowed from sheet functions.

How exactly are you using that function?

As I said functions called from worksheets do not allow alteration or selection of other cells.

Apart from that in your function was does LatNow refer to?
What would the selection be referring to?
Why a udf function rather than using VLOOKUP or MATCH?

If you are using from a cell it will need to be a function in order to return a value to the cell.

You can use the find function but not the .Activate part.

dim rngFind as range

set rngFind = Selection.Find(What:=LatNow, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

CurrentRow = rngFind.Row

Note the Selection and Activecell references will also need to change in order for the code to work properly from worksheet.