Function fails on worksheet

[Adrian]

I have written this function - it works fine in the
immediate window of the VBA editor, but fails when I put
into a cell of a worksheet. I get #VALUE.
Why?

(Explanation - an area of the worksheet "modules" contains
a database which has multiple keyvalues. This function
allows you to specify in which column to look for a value,
then looksup the answer. kinda like "vlookup" with extra
flexibility.)

Function Minfo(lookupcol, lookupval, returncol)
Set src = Workbooks("Data.xls").Names
("Mdatabase").RefersToRange
toprow = src.Row
lastrow = src.Rows.Count + toprow - 1
'find lookupcol in toprow
lkcol = Workbooks("Data.xls").Worksheets("modules").Range
(toprow & ":" & toprow).Find(lookupcol).Column
'find returncol in toprow
rtncol = Workbooks("Data.xls").Worksheets("modules").Range
(toprow & ":" & toprow).Find(returncol).Column
'find lookupval in lookupcol and give its row
lkrow = Workbooks("Data.xls").Worksheets("modules").Range
(Cells(1, lkcol), Cells(lastrow, lkcol)).Find
(lookupval).Row

Minfo = Workbooks("Data.xls").Worksheets("modules").Cells
(lkrow, rtncol)
End Function

 

[Tom Ogilvy]

Before xl2002, the FIND method does not work in User Defined Functions used
in a worksheet.

Is that your problem? (using an earlier version of Excel than 2002)

 

[Adrian]

Doh!
Yep. XL 2000.
Can you suggest a workaround? (Other than upgrading!)

 

[Tom Ogilvy]

if you are looking in a single column or single row, you can use
Application.match

for a column in a single row
Dim rng as range, res as variant, rng1 as Range
set rng = Range(cells(1,1),cells(1,200))
set res = Application.Match(colName,rng,0)
if not iserror(res) then
set rng1 = rng(1,res)
end if

for a row in a single column
Dim rng as range, res as variant, rng1 as Range
set rng = Range(cells(1,1),cells(200,1))
set res = Application.Match(colName,rng,0)
if not iserror(res) then
set rng1 = rng(res)
end if