.Find function

[citizenthom]

I'm trying to use the .find function on a range in a vba user defined function.
I've found though the .find does not work in a user defined function for vba on mac osx, even when the same code works on a windows platform.

It's strange as .find appears to work with sub routines, but with UDFs it causes the function to exit with no error messages

Has anyone else noticed this and found a solution.

[mikerickson]

I've found that there are several methods that don't work when called from a worksheet, but do work when called from VBA.

.Find
.SpecialCells
.NavagateArrow

are the ones at the top of my mind.

In most of the cases, looping will serve as a workaround.

[nsaint]

I am also trying to use the .find method and running into problems. Seems to work on Windows fine, but will not work on Mac. It doesn't seem as if the libraries on the mac have this built in. Am I missing something? Is there another way to use some sort of VBA .find method to search through a range of cells and assigning the fund value to a variable or the offset to the found value to that variable

[mikerickson]

Rather than

Set foundCell = someRange.Find("search term")

you could use

Set foundCell = Nothing
For each oneCell in SomeRange
    If oneCell.Value = "search term" Then Set foundCell = oneCell: Exit Sub
Next oneCell

BTW, the .Find method of a Range works fine on my Mac when used in VBA subs. Just not when in a UDF that is called by a worksheet formula.

[nsaint]

Rather than

Set foundCell = someRange.Find("search term")

you could use

Set foundCell = Nothing
For each oneCell in SomeRange
    If oneCell.Value = "search term" Then Set foundCell = oneCell: Exit Sub
Next oneCell

BTW, the .Find method of a Range works fine on my Mac when used in VBA subs. Just not when in a UDF that is called by a worksheet formula.

Thanks for the reply...Yes, I used a for each loop to get it to work and yes it is a UDF called from a worksheet formula.