[Help Needed] Custom Lookup Function

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

Please Login or Register  to view this content.

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???

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.

"[email protected]" 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???
>
> --
> [email protected]
> ------------------------------------------------------------------------
> [email protected]'s Profile: http://www.excelforum.com/member.php...o&userid=26151
> View this thread: http://www.excelforum.com/showthread...hreadid=394788

--

Dave Peterson