Hello

I'm writing a custom lookup function to deal with certain requirement.
This is my use case.

I've an Excel Workbook with multiple sheets. First Sheet is the "Master
Data". In one of the other sheets I've a value which I want to look up
in "Master Data" sheet and get the row number. Simple.

I've written this function


Code:
--------------------

Function foo(lookup As Range)
Dim foundCell As Range

With Sheets("Master Data").UsedRange
Set foundCell = .Find(lookup.Value)
If Not foundCell Is Nothing Then
foo = foundCell.Address
Else
foo = " :( "
End If
End With

End Function

--------------------


But for some reason my function always returns :(, meaning it didn't
find the value in the "Master Data" sheet. I'm pretty sure it is
there.

Same Function when converted to SUB and with hard coded lookup value
returns correctly.

Please help me out... what am I missing???


--

------------------------------------------------------------------------
's Profile: http://www.excelforum.com/member.php...o&userid=26151
View this thread: http://www.excelforum.com/showthread...hreadid=394788