Strange phenomena when use function

**Andy Pope** · 01-10-2012, 08:45 AM

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?

**SpringLily** · 01-10-2012, 09:41 AM

The code itself is rather long (>100 sentences). I only quoted the line which caused problem. LatNow is defined before the line. Shall I use a sub instead of a function?

**Andy Pope** · 01-10-2012, 09:55 AM

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.

**SpringLily** · 01-10-2012, 10:02 AM

The reason for me to use .activate part it to get the row number of the searched results. Below are the two lines from my function.

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

CurrentRow = ActiveCell.Row

how do I modify the code to make it work?

Thanks.

Regards,
Lily

**Andy Pope** · 01-10-2012, 10:07 AM

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.