Error Handling with Worksheetfunction.Match

[mikelee101]

Hello,
I am having trouble with error handling. Not sure how much of the code to
include here, as I can’t really tell where the issue is. Here’s the sub
routine where it’s breaking down:
===========
MoveData:
On Error GoTo BadProjectName
ToRowNum = Application.WorksheetFunction.Match(ProjNme,
ToRng.Columns(1).Cells, 0)

On Error GoTo IrregularVendor
ToColNum = Application.WorksheetFunction.Match(VendNme, ToRng.Rows(1).Cells,
0) ***err on this line***

On Error GoTo 0
ToRng.Cells(ToRowNum, ToColNum).Value = Rslt

IrregularVendor:
On Error GoTo 0
Return
============
The problem is that, the second time that it encounters an irregular vendor,
I get a run time error (Unable to get the Match property of the
WorksheetFunction class). The fact that it works once leads me to believe
that some sort of setting is being retained, but I can't figure out what it
could be. I’ve tried inserting err.clear in various places, with no luck.
If anyone has any suggestions, please let me know.

Excel07, XPPro, VB6.5

 

[Dave Peterson]

I would just drop the On Error stuff and .worksheetfunction and use:

Dim ToRowNum as Variant 'not long, it could be an error
dim ToColNum as variant 'not long

torownum = application.match(projnme, torng.columns(1), 0)
tocolnum = application.match(vendnme, torng.rows(1),0)

if iserror(torownum) _
or iserror(tocolnum) then
msgbox "at least one mismatch"
else
ToRng.Cells(ToRowNum, ToColNum).Value = Rslt
end if

 

[Dave Peterson]

ps. Clear the error (err.clear) and then try Resume Next.

pps. I like the Variants, using application.match() and checking for errors
better. I think it's easier to understand.

 

[Patrick Molloy]

MATCH, like VLOOKUP and HLOOKUP functions raise an error when used but
there's no match

my preference is to create a UDF to handle these
With Match, returning a zero would indicate no match

so
ToColNum = Application.WorksheetFunction.Match(VendNme, ToRng.Rows(1).Cells,

0)

would become
ToColNum = SafeMatch(VendNme, ToRng.Rows(1).Cells)
If ToColNum=0 then
' handle the no match found condition
end if


in you module add this
FUNCTION SafeMatch(what as string, where as range) as long
on error resume next
SafeMatch = Application.WorksheetFunction.Match(what,where,false)
on error goto 0
END FUNCTION