Strange phenomena when use function

[SpringLily]

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?

Please Login or Register  to view this content.

Thanks in advance.

Regards,
Lily

[Andy Pope]

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?

[SpringLily]

Hi, Andy
I used the function by the equation below for a cell:

=IF(INSomething<>"",INSomething,FindString)

where InSomething is a defined name and FindString is the function for finding the right information. The code for the function is listed above.

Thanks.
Regards,
Lily

[Andy Pope]

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]

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]

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]

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.

Please Login or Register  to view this content.

how do I modify the code to make it work?

Thanks.

Regards,
Lily

[Andy Pope]

Please Login or Register  to view this content.

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

[SpringLily]

Thanks. It works! Regards, Lily