Find a value in a table and return the cell reference
The following modification will return the row and column numbers of the
specified value; e.g., 2, 2
Public Function MyFunction(ByVal FindValue As Variant, _
SearchRange As Range) As String
On Error Resume Next
MyFunction = SearchRange.Find(What:=FindValue, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False).Row & ", " & _
SearchRange.Find(What:=FindValue, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False).Column
On Error GoTo 0
End Function
Alan Beban
Jim Thomlinson wrote:
You can give this a try. This is a UDF so the code must be placed in a
standard code module (not a sheet or ThisWorkbook).
Public Function MyFunction(ByVal FindValue As Variant, _
SearchRange As Range) As Range
on error resume next
Set MyFunction = SearchRange.Find(What:=FindValue, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
on error Goto 0
End Function
It returns the cell that contains the value you are looking for... You can
use it in a cell like this...
=MyFunction("abc", A1:C3)
One note is that whiel the function returns the cell where the value was
found the result will just be abc since that is what that cell contains so I
am not to sure how this help you...
|