.Find doesn't work in UDF but works in a Sub

May 27, 2011 at 17:50:01
Specs: Macintosh , Intel Core5 i5/4 GB
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.

'Sub FT()
Public Function FindTest( _
FindThis As Variant) _
As Variant

'Dim FindThis As String
'Dim FindTest As Variant
'FindThis = "Total"

With Worksheets("A").Range("$A:$A")
Set rngFind = .Find(FindThis, LookIn:=xlValues, _
MatchCase:=False, LookAt:=xlWhole)
End With
If Not rngFind Is Nothing Then
'match found
FindTest = "A" & "!" & rngFind.Address
FindTest = "Not Found"
End If
MsgBox (FindTest)
End Function

See More: .Find doesnt work in UDF but works in a Sub

Report •

May 28, 2011 at 07:13:49
I can't test this on a Mac, but I was able to test it in Excel 2010.

It seems to work fine.

What are you entering in Excel when you try to use the UDF?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 31, 2011 at 08:38:34
For the function, I enter =FindTest("Total") and I get Not Found. If I run the Sub, it tells me the cell in which the string Total is located. So for some reason the .Find in FindTest as a UDF doesn't appear to work but as a regular macro it does.

Thanks for the response.

Report •

May 31, 2011 at 10:12:32
As I said, it works fine as both a sub and a UDF on Excel for Windows.

I don't have a Mac that can run macros, so I can't offer any assistance in that regard.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

May 31, 2011 at 16:58:22
I appreciate your help. After seeing your reply I tried this on a Windows PC at work and, as you discovered, it works. So it must be either a difference in my configuration or a bug in Mac for Excel.

Report •

Ask Question