[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

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

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.

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

--

Dave Peterson