|I've been trying, without success, to implement the UDF in "Lookup across multiple sheets (40+)." |
I'm using Excel for Mac 2011 on a MacBook Pro (10.6.7). What I've found is that the Find method doesn't work if called from a UDF but will work if called from a Sub().
To demonstrate this you'll need a Sheet named A with the word Total on it someplace in column A.
Below is some example code. It's both a UDF and a Sub depending on what's commented out. As a UDF, the function returns "Not Found" even though the value to be found is in column A. However, if you comment out the 3 lines that define the UDF and uncomment the Sub and the three Dim statements, the Sub works.
Any insight or help would be appreciatd.
Public Function FindTest( _
FindThis As Variant) _
'Dim FindThis As String
'Dim FindTest As Variant
'FindThis = "Total"
Set rngFind = .Find(FindThis, LookIn:=xlValues, _
If Not rngFind Is Nothing Then
FindTest = "A" & "!" & rngFind.Address
FindTest = "Not Found"