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

When you do a find (either manually or via code), excel remembers the last
settings (part vs whole, matchcase, all that stuff).

You might have better luck if you specify all the settings you want in your
..find statement.

" wrote:

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

--

Dave Peterson