.Find in Sub and Function ...

  • Thread starter Thread starter âÒÁÝÉÎ ëÏÎÓÔÁÎÔÉÎ í.
  • Start date Start date
Â

âÒÁÝÉÎ ëÏÎÓÔÁÎÔÉÎ í.

hi
got such problem: if i'm using Find in Procedure everything works fine.
if i'm trying to use it in Function - it returns Nothing
my goal is to catch number of the row containing needed value

sub FindVal ()
Sheets(1).Cells(1,1).Value= Range("B:B").Find(What:="sText", LookIn
:=xlValues).row
end sub

Function FindVal(ByVal sRng as Range, sText as string) as string
FindVal= CStr(sRng.Find(What:= sText, LookIn:= xlValues).Row)
end function

anyone can help me with it ?
ps: using excell 200
 
A UDF (Use Defined Function, that is, a VB function called by a worksheet
formula) is not allowed to perform "actions". Do a Find is an action I'm
afraid. You might try using worksheet functions like Match and VLookup.
 
xl2002 will allow you to do a find in a UDF called from a worksheet. (Maybe
time for an upgrade, although I don't know if it's been changed in xl2003.)

Here's an example using application.match (like Jim suggested):

Option Explicit
Sub testme()

Dim res As Variant

With ActiveSheet
res = Application.Match("sText", .Range("a:a"), 0)
If IsError(res) Then
MsgBox "not found"
Else
MsgBox "Found on row: " & res
End If
End With
End Sub
 
As you've learned, Find will not work in a function. Try it this way instead:

Function FindVal(ByVal sRng As Range, sText as String) As Variant
FindVal = Application.Match(sText, Columns(2), 0)
End Function

The function is defined as returning a variant: if sText isn't found in column B, Match will
return an error value, #NA()!. If it is found, it will return the row number.
 
Back
Top