Using Find in a UDF

[Wheels1978]

Hi guys,
It's been a while, but here I am again with something I can't get to work.

I want to lookup a value on the current sheet (Summary) in sheet "Refs" and return the address of the cell on sheet "Refs" that contains the value.
I want to use the Find method for this, so that I can also use FindNext later.

The sub I created may not be nice, but it works. It looks like this:

Sub Test()
Dim SourceRange As Range, InputRange As Range, FindMonth As String
Set InputRange = Worksheets("Summary").Range("A2")
Set SourceRange = Worksheets("Refs").Columns("A:A").Find(InputRange.Value, LookIn:=xlValues)
FindMonth = SourceRange.Address
MsgBox FindMonth
End Sub


Now I want to convert the sub to a UDF. What I created gives me the #VALUE error :confused: . Can anyone tell me why and what I should change?

Function FindMonth(InputRange As Range) As String
With Worksheets("Refs").Columns("A:A")
FindMonth = .Find(InputRange.Value, LookIn:=xlValues).Address
End With
End Function

Thanks a lot for any help!

[NateO]

FindMonth() works for me. :confused:

You're probably not getting a match, which will error out, i.e., there is no address for a Range that does not exist...

So, try to set a Range Object with the Find Method, and if your Object comes back as Nothing, do not try to pass an address to the function. ;)

And, why not just use Match()? E.g.,


="A"&MATCH(B4,A:A,0)

[Kluz]

Certain functions won't work in a UDF, Find is one of them as is SpecialCells. It's a hassle, but workarounds like Nate provided can provide the same functionality.

[NateO]

I agree with Special Cells, but just to be sure, I had no problem using the Find Method in a UDF, with Excel 2003, as such:


Public Function FindMonth( _
ByRef InputRange As Range, _
ByRef srchRng As Range) As String
Dim tmpRng As Range
Set tmpRng = srchRng.Find( _
InputRange.Value, LookIn:=xlValues)
If Not tmpRng Is Nothing Then
Let FindMonth = tmpRng.Address
Set tmpRng = Nothing
Else: Let FindMonth = "No find, d00d"
End If
End Function

Sub foo()
With Worksheets("Refs")
MsgBox FindMonth(.Range("B1"), .Range("A:A"))
End With
End Sub
And:


=findmonth(B1,Refs!A:A)

You probably want to make Columns("A:A") a function argument, as this isn't volatile, and it will only update on changes in A:A if it's an argument...

But, I'd probably still go with Match() ;)

[Mike Rosenblum]

Yeah, the list of commands that can modify the cursor and therefore are off limits to User Defined Function (UDFs) include: SpecialCells, CurrentRegion, CurrentArray, Find (xl 9.0 and below), Replace, GOTO, SELECT, PRECEDENTS, AutoFilters, AdvancedFilters, etc..

Surprisingly, you can use Range.End() within a UDF. And Range.Find() can be used as well, but only as of Excel 2002 (Excel 10.0). Previous versions will throw an error. Bummer.

I'd use Match() instead, as Nate suggested above. If you need to test for partial-values within the Cell, then Match can make use of wildcards, such as "*", etc.

[Wheels1978]

Thanks a lot for this info.
Of course, Help and MSDN (where I looked first) don't mention this restriction in UDF's.

Actually, I wanted to use FindNext as well. So much for that. I'll stick with Match to find the first match to my data. The table I'm looking at is designed to have the next match in the row after the first. FindNext would have been nice if somehow there are rows in between matches.

Thanks again.

Oh, and uhm... I do feel honoured that my question is answered only by forum leaders. You guys must spend a great part of your life here.