PDA

View Full Version : .Find function



citizenthom
08-08-2012, 01:29 PM
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
08-11-2012, 09:48 AM
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
11-10-2014, 03:17 PM
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
11-11-2014, 01:03 PM
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
11-11-2014, 02:59 PM
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.