howto use range variables in functions

[Claude]

I am trying to find to create a function that will perform
a search operation on a given worksheet range. I just
can't get this to work:

Function testfindfunction(valuetofind, rangetosearch)

testfindfunction = rangetosearch.Find(What:=valuetofind,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
Matchcase:=False).Address
MsgBox (testfindfunction & " function")

End Function


The function works fine when called from a macro (see
below), but not when used in a worksheet. Any ideas on
what's wrong? I'm using excel97.


Sub macro1()

Dim rangetosearch As range
Set rangetosearch = Selection
MsgBox (testfindfunction(522602, Selection) & "
functioncalled")

End Sub

 

[Leo Heuser]

Claude

Unfortunately you can't use "Find" in a
user defined function (UDF).

 

[Charles Williams]

Hi Claude,

Excel97 will not let you use Find in a UDF. (you can use MATCH or VLOOKUP)


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Kevin Beckham]

Your only solution is a kludge like

Function testfindfunction(valuetofind, rangetosearch As
Range) As String
Dim rngFind As Range

testfindfunction = "not found"

For Each rngFind In rangetosearch
If rngFind.Value Like "*" & valuetofind & "*" Then
testfindfunction = rngFind.Address
Exit For
End If
Next rngFind
Set rngFind = Nothing
End Function

Kevin Beckham