Hello,
I am having trouble with error handling. Not sure how much of the code to
include here, as I cant really tell where the issue is. Heres the sub
routine where its 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. Ive tried inserting err.clear in various places, with no luck.
If anyone has any suggestions, please let me know.

Excel07, XPPro, VB6.5
--
Mike Lee
McKinney,TX USA

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



mikelee101 wrote:

Hello,
I am having trouble with error handling. Not sure how much of the code to
include here, as I cant really tell where the issue is. Heres the sub
routine where its 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. Ive tried inserting err.clear in various places, with no luck.
If anyone has any suggestions, please let me know.

Excel07, XPPro, VB6.5
--
Mike Lee
McKinney,TX USA

--

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.

mikelee101 wrote:

Hello,
I am having trouble with error handling. Not sure how much of the code to
include here, as I cant really tell where the issue is. Heres the sub
routine where its 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. Ive tried inserting err.clear in various places, with no luck.
If anyone has any suggestions, please let me know.

Excel07, XPPro, VB6.5
--
Mike Lee
McKinney,TX USA

--

Dave Peterson

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,fal se)
on error goto 0
END FUNCTION




"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Hello,
I am having trouble with error handling. Not sure how much of the code to
include here, as I cant really tell where the issue is. Heres the sub
routine where its 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. Ive tried inserting err.clear in various places, with no luck.
If anyone has any suggestions, please let me know.

Excel07, XPPro, VB6.5
--
Mike Lee
McKinney,TX USA