Make udf flexible to specific sheet

Hello everyone
I have the following UDF that I need to fix to be flexible with any sheet not only active sheet
It is searching for the last value in specific range

Please Login or Register  to view this content.

It is working well when I use it in the active sheet

Please Login or Register  to view this content.

I need to be able to use the UDF in another sheet

Please Login or Register  to view this content.

Thanks advanced for help

The unqualified use of the cells() method in the If statement means that VBA will use the active sheet. Qualify your use of the cells method to a specific range object. I assume you would want it to operate on the myrng object passed to the function.

Please Login or Register  to view this content.

In the long run, I find there is value in never (or almost never) using unqualified references to methods and properties -- especially in UDFs. I would suggest that you get in the habit of always explicitly specifying the object that a property or method applies to.

Thanks for reply
I tried using Parent to refer to the sheet but got VALUE error

Please Login or Register  to view this content.

Parent is a property not an object: https://msdn.microsoft.com/en-us/lib.../ff196885.aspx

myrng.parent...

Is it necessary for the UDF to place the myrng object within its parent object (the sheet)? If I understand what you are doing, why not simply loop through the individual cells of the myrng object?

Please Login or Register  to view this content.

It probably has trouble if mrng can be a 2D range, but if myrng will always be a 1D range as your example shows, then this should work.

Thanks I tried your suggestion. It works but didn't find (not found) although the string is existing ...

At first post it was working in the same sheet I was searching in and not working in another sheet
Now it gives (Not Found) on both sheets

Please Login or Register  to view this content.

Thank you very much for these great solutions. Now it is working as expected
Thanks a lot for help
Best and Kind Regards

You're welcome.

shg, I think that he wanted a reverse lookup match rather than a forward lookup? He has a way to do both now.

I normally add an Application.Volatile False for UDFs.
e.g.

Please Login or Register  to view this content.

but I tested shg udf and give me the last occurrence as expected

@Kenneth: I just had the same thought. OTOH, the other code only looks at the first column of the range. So perhaps

Please Login or Register  to view this content.

That's weird. At first it gives me right result but after testing it again , it gives me the first occurrence not the last
Generally I used the UDF in post #10 and it is ok now

Thank you very much Mr. shg for working and great solution. It is effective
Thanks a lot Mr. Kenneth for sharing us. It is my pleasure to contribute in my thread
Regards

You're welcome.