Match problems.

[Adrian]

I have a function in development that is giving me trouble.
The line beginning "lkcol =" works fine.
The line beginning "rtncol =" gives the error message "run
time error 1004, Unable to get the Match property of the
WorksheetFunction class."


Function Minfo(lookupkey, lookupval, rtnkey)
Set rng = Workbooks("data.xls").Worksheets("staff").Range
("1:1")
lkcol = Application.WorksheetFunction.Match(lookupkey,
rng, 0)
rtncol = Application.WorksheetFunction.Match(rtnkey, rng,
0)
MsgBox lkcol & " " & rtncol
' more to go in here
Minfo = 0
End Function

 

[Bob Phillips]

Adrian,

Seeing as the first works okay, that means that rng is fine, so it must just
mean that rtnkey is not found in rng

You could trap this like so

On Error Resume Next
rtncol = WorksheetFunction.Match(rtnkey,rng, 0)
If Err.Number = 1004 Then
MsgBox "Not Found"
End If
On Error GoTo 0
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Patrick Molloy]

If no match is found then the function raises an error -
as do VLOOKUP() and HLOOKUP()
Make sure that you have
Option Explicit at the first line of your modules.
You pass three parameters to your function, but use two,
and there are two variables not defined.

The error often occurs when the code tries to set a range
varaible to a range otr sheet that doesn't exist