Error Handling - On Error GoTo doesn't trap error successfully

When you use the "On Error GoTo" approach to trap an error, is it
necessary to "clear out" the error in some way before a later instance
of the same type of error can be trapped?

I have a program that loops through an excel table, and the error
handling seems to work the first time bad data is encountered (in this
case, when the cell has a string "N/A" rather than the expected
currency data type) but then when it hits a second instance of the same
error (Run TIme Error '13' Type Mismatch) it stops dead in its tracks.

Any suggestions?

From your description of the problem you should be handling the #N/A in the
normal flow of execution, not an errorhandler (IMO). If you can reasonably
expect a problem it is not an error. It seems to me that with

if not isnumeric('whatever you are looking at') then

Could avoid throwing the error in the first place.

That being said, you can not catch an error within an error handler, so if
an error is being thrown while you are in the errorhandler then you need to
rethink your error handler. To clear an error use...

err.clear

If you need more help you will have to post some code.
--
HTH...

Jim Thomlinson


"David" wrote:

> When you use the "On Error GoTo" approach to trap an error, is it
> necessary to "clear out" the error in some way before a later instance
> of the same type of error can be trapped?
>
> I have a program that loops through an excel table, and the error
> handling seems to work the first time bad data is encountered (in this
> case, when the cell has a string "N/A" rather than the expected
> currency data type) but then when it hits a second instance of the same
> error (Run TIme Error '13' Type Mismatch) it stops dead in its tracks.
>
> Any suggestions?
>
>

Jim - Thanks for the reply. I've put in the relevant section of code
below but let me try to describe the situation more clearly to spare
you the time to wade through the whole listing. I appreciate your time
and will be as concise as I can.

I have programmed a procedure to go to Yahoo's quote server and pull
down quotes. The quoteserver sends back a CSV file that I've parsed
into columns. This creates a table which has a row for each ticker and
a column for each data item about the ticker, e.g. Open, High, Low,
Close, Volume, CompanyName, and so on. This procedure works fine.

If Yahoo does not have a certain data item, then the quote server sends
down the string "N/A" This is simply a string, not to be confused with
the excel error code which can be detected with ISNA().

The next procedure I wrote (excerpted below) attempts to retrieve the
value from the page where Yahoo puts it and assign it to a variable.
The error happens in this line or ones like it:

cLow = RetrieveData(rRetrData, sTicker, "low")

where RetrieveData() is a function that I wrote which takes a range,
column name and row name and finds the data item sought. sTicker is a
string variable which is the row name and "Low" is the header for the
column.

The RetrieveData function goes to work and comes back with a string
"N/A" which of course does not fit into the variable cLow which is Dim
as Currency. This causes the Type Mismatch Error.

All I really need my procedure to do is to stop trying to process that
particular ticker, and skip to the next ticker.

Any suggestions as to better ways to make the program get past the
missing data would be highly appreciated. I know I could just Dim the
variables as variants instead of currency but that just makes the
problem resurface downstream in later processing when other procedures
try to do mathematical operations on the data.





Sub CalculateIntradayResults()

Dim cLast As Currency 'last price
Dim cHigh As Currency 'intraday high from Yahoo, from the current
refresh cycle
Dim cLow As Currency 'intraday Low from Yahoo from the current
refresh cycle
Dim cPriorHigh As Currency ' intraday high from the previous
refresh cycle
Dim cPriorlow As Currency 'intraday low from the previous refresh
cycle
Dim cPriorHighSinceLow 'highest price since intraday low in prev
refresh cycle
Dim cPriorLowSinceHigh ' Lowest price since intraday high in prev
refresh cycle

'step through the retrieved data one ticker at a time and extract
values to variables
For iRow = iFirstRow To iLastRow
sTicker = rIntraCalc.Cells(iRow, 1)
On Error GoTo MissingData
cLast = RetrieveData(rRetrData, sTicker, "Last")
On Error GoTo MissingData
cHigh = RetrieveData(rRetrData, sTicker, "High")
On Error GoTo MissingData
cLow = RetrieveData(rRetrData, sTicker, "low")
On Error GoTo MissingData
cPriorHigh = RetrieveData(rIntraCalc, sTicker, "IntraHigh")
On Error GoTo MissingData
cPriorlow = RetrieveData(rIntraCalc, sTicker, "IntraLow")
On Error GoTo MissingData
cPriorHighSinceLow = RetrieveData(rIntraCalc, sTicker,
"HiSinceLow")
On Error GoTo MissingData
cPriorLowSinceHigh = RetrieveData(rIntraCalc, sTicker,
"LowSinceHi")

'once all the data has been captured in variables, test some
conditions and record results
If cLast > cPriorHigh Then
[skipped code not relevant to this problem]
End If

[ skipped code not relevant to this problem]

GoTo ResumeWithoutError: ' this skips the error message below
if no problems.
MissingData:
'MsgBox "missing data problem with " & sTicker & " in calculate
intraday results"
' this will fire once at the end of run because the last cell
is blank.
ResumeWithoutError:
Next iRow

End Sub

Jim - Thanks for the reply. I've put in the relevant section of code
below but let me try to describe the situation more clearly to spare
you the time to wade through the whole listing. I appreciate your time
and will be as concise as I can.

I have programmed a procedure to go to Yahoo's quote server and pull
down quotes. The quoteserver sends back a CSV file that I've parsed
into columns. This creates a table which has a row for each ticker and
a column for each data item about the ticker, e.g. Open, High, Low,
Close, Volume, CompanyName, and so on. This procedure works fine.

If Yahoo does not have a certain data item, then the quote server sends
down the string "N/A" This is simply a string, not to be confused with
the excel error code which can be detected with ISNA().

The next procedure I wrote (excerpted below) attempts to retrieve the
value from the page where Yahoo puts it and assign it to a variable.
The error happens in this line or ones like it:

cLow = RetrieveData(rRetrData, sTicker, "low")

where RetrieveData() is a function that I wrote which takes a range,
column name and row name and finds the data item sought. sTicker is a
string variable which is the row name and "Low" is the header for the
column.

The RetrieveData function goes to work and comes back with a string
"N/A" which of course does not fit into the variable cLow which is Dim
as Currency. This causes the Type Mismatch Error.

All I really need my procedure to do is to stop trying to process that
particular ticker, and skip to the next ticker.

Any suggestions as to better ways to make the program get past the
missing data would be highly appreciated. I know I could just Dim the
variables as variants instead of currency but that just makes the
problem resurface downstream in later processing when other procedures
try to do mathematical operations on the data.





Sub CalculateIntradayResults()

Dim cLast As Currency 'last price
Dim cHigh As Currency 'intraday high from Yahoo, from the current
refresh cycle
Dim cLow As Currency 'intraday Low from Yahoo from the current
refresh cycle
Dim cPriorHigh As Currency ' intraday high from the previous
refresh cycle
Dim cPriorlow As Currency 'intraday low from the previous refresh
cycle
Dim cPriorHighSinceLow 'highest price since intraday low in prev
refresh cycle
Dim cPriorLowSinceHigh ' Lowest price since intraday high in prev
refresh cycle

'step through the retrieved data one ticker at a time and extract
values to variables
For iRow = iFirstRow To iLastRow
sTicker = rIntraCalc.Cells(iRow, 1)
On Error GoTo MissingData
cLast = RetrieveData(rRetrData, sTicker, "Last")
On Error GoTo MissingData
cHigh = RetrieveData(rRetrData, sTicker, "High")
On Error GoTo MissingData
cLow = RetrieveData(rRetrData, sTicker, "low")
On Error GoTo MissingData
cPriorHigh = RetrieveData(rIntraCalc, sTicker, "IntraHigh")
On Error GoTo MissingData
cPriorlow = RetrieveData(rIntraCalc, sTicker, "IntraLow")
On Error GoTo MissingData
cPriorHighSinceLow = RetrieveData(rIntraCalc, sTicker,
"HiSinceLow")
On Error GoTo MissingData
cPriorLowSinceHigh = RetrieveData(rIntraCalc, sTicker,
"LowSinceHi")

'once all the data has been captured in variables, test some
conditions and record results
If cLast > cPriorHigh Then
[skipped code not relevant to this problem]
End If

[ skipped code not relevant to this problem]

GoTo ResumeWithoutError: ' this skips the error message below
if no problems.
MissingData:
'MsgBox "missing data problem with " & sTicker & " in calculate
intraday results"
' this will fire once at the end of run because the last cell
is blank.
ResumeWithoutError:
Next iRow

End Sub

First off with your errorhandler, you only need to write it once at the top
of the code. Once set this is what the program will do in case of an error
unless you specify otherwise. Something like this

Sub Whatever()
On Error goto Errorhandler 'Ineffect until otherwise stated
'Do this that and the other thing
'Now we are going to do something that will trow and error somethimes
on error resume next 'Now this is in effect
set myWorkbook = workbooks("MyBook.xls") 'Error if this file is not open
on error got errorhandler 'Back to the original error handler
if myWorkbook is nothing then
workbooks.open("C:\MyBook.xls")
set myWorkbook = workbooks("MyBook.xls")
endif
ErrorHandler:
end sub

Your function RetrieveData returns a variant I suspect? If not it probably
should. Then perhaps something more like this will work.

dim varReturnValue as variant
varReturnValue = RetrieveData(rRetrData, sTicker, "low")
if is numeric(varReturnValue) then
cLow = cdbl(varReturnValue)
else
'Do something else...
end if
--
HTH...

Jim Thomlinson


"David" wrote:

> Jim - Thanks for the reply. I've put in the relevant section of code
> below but let me try to describe the situation more clearly to spare
> you the time to wade through the whole listing. I appreciate your time
> and will be as concise as I can.
>
> I have programmed a procedure to go to Yahoo's quote server and pull
> down quotes. The quoteserver sends back a CSV file that I've parsed
> into columns. This creates a table which has a row for each ticker and
> a column for each data item about the ticker, e.g. Open, High, Low,
> Close, Volume, CompanyName, and so on. This procedure works fine.
>
> If Yahoo does not have a certain data item, then the quote server sends
> down the string "N/A" This is simply a string, not to be confused with
> the excel error code which can be detected with ISNA().
>
> The next procedure I wrote (excerpted below) attempts to retrieve the
> value from the page where Yahoo puts it and assign it to a variable.
> The error happens in this line or ones like it:
>
> cLow = RetrieveData(rRetrData, sTicker, "low")
>
> where RetrieveData() is a function that I wrote which takes a range,
> column name and row name and finds the data item sought. sTicker is a
> string variable which is the row name and "Low" is the header for the
> column.
>
> The RetrieveData function goes to work and comes back with a string
> "N/A" which of course does not fit into the variable cLow which is Dim
> as Currency. This causes the Type Mismatch Error.
>
> All I really need my procedure to do is to stop trying to process that
> particular ticker, and skip to the next ticker.
>
> Any suggestions as to better ways to make the program get past the
> missing data would be highly appreciated. I know I could just Dim the
> variables as variants instead of currency but that just makes the
> problem resurface downstream in later processing when other procedures
> try to do mathematical operations on the data.
>
>
>
>
>
> Sub CalculateIntradayResults()
>
> Dim cLast As Currency 'last price
> Dim cHigh As Currency 'intraday high from Yahoo, from the current
> refresh cycle
> Dim cLow As Currency 'intraday Low from Yahoo from the current
> refresh cycle
> Dim cPriorHigh As Currency ' intraday high from the previous
> refresh cycle
> Dim cPriorlow As Currency 'intraday low from the previous refresh
> cycle
> Dim cPriorHighSinceLow 'highest price since intraday low in prev
> refresh cycle
> Dim cPriorLowSinceHigh ' Lowest price since intraday high in prev
> refresh cycle
>
> 'step through the retrieved data one ticker at a time and extract
> values to variables
> For iRow = iFirstRow To iLastRow
> sTicker = rIntraCalc.Cells(iRow, 1)
> On Error GoTo MissingData
> cLast = RetrieveData(rRetrData, sTicker, "Last")
> On Error GoTo MissingData
> cHigh = RetrieveData(rRetrData, sTicker, "High")
> On Error GoTo MissingData
> cLow = RetrieveData(rRetrData, sTicker, "low")
> On Error GoTo MissingData
> cPriorHigh = RetrieveData(rIntraCalc, sTicker, "IntraHigh")
> On Error GoTo MissingData
> cPriorlow = RetrieveData(rIntraCalc, sTicker, "IntraLow")
> On Error GoTo MissingData
> cPriorHighSinceLow = RetrieveData(rIntraCalc, sTicker,
> "HiSinceLow")
> On Error GoTo MissingData
> cPriorLowSinceHigh = RetrieveData(rIntraCalc, sTicker,
> "LowSinceHi")
>
> 'once all the data has been captured in variables, test some
> conditions and record results
> If cLast > cPriorHigh Then
> [skipped code not relevant to this problem]
> End If
>
> [ skipped code not relevant to this problem]
>
> GoTo ResumeWithoutError: ' this skips the error message below
> if no problems.
> MissingData:
> 'MsgBox "missing data problem with " & sTicker & " in calculate
> intraday results"
> ' this will fire once at the end of run because the last cell
> is blank.
> ResumeWithoutError:
> Next iRow
>
> End Sub
>
>

One thing I would suggest ... in the error handling routine do you
have:

Goto xxx

OR

Resume xxx

If you have goto then the error routine will not catch a second error,
as it is technically still in the error handling step.

Thanks, Jim and Bill. That helps enormously.
-David

I am having this problem and it is because of a GoTo in the error handler.
The problem is that I need to stop the code on the error and go back to a
specific point in the macro or I get stuck in a loop. Below is a brief piece
of my code. Any help would be greatly appreciated.

Sub abc()

Dim.........
Set Variable Values............

Res1:
Do While Range("PathList!A" & v_pathrow).Value <> Empty

v_path = Range("PathList!A" & v_pathrow).Value
On Error GoTo ErrorLg
Workbooks.Open Filename:=v_path '*****Point of
most errors
Sheets("Cover Page").Select
v_cc = Range("c16").Value
v_currow = 1
Do While Range("F" & v_currow).Value <> "Total Count"
v_currow = v_currow + 1
Loop '***Loop I get stuck in


ActiveWindow.Close (False)


v_pasterow = v_pasterow + 1
v_pathrow = v_pathrow + 1
Loop
Rows(v_pasterow & ":" & v_pasterow + 1).Select
Range("A18").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Exit Sub

ErrorLg:
Range("ErrorLog!A" & v_errorrow).Value = v_path
v_errorrow = v_errorrow + 1
v_pathrow = v_pathrow + 1
Rows(v_pasterow & ":" & v_pasterow).Select
Selection.Delete Shift:=xlUp
Err.Clear
GoTo Res1
--
Thank You,
Steve


"Bill Schanks" wrote:

> One thing I would suggest ... in the error handling routine do you
> have:
>
> Goto xxx
>
> OR
>
> Resume xxx
>
> If you have goto then the error routine will not catch a second error,
> as it is technically still in the error handling step.
>
>

Use Resume, not Goto, in your error handling block.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Steve D" <[email protected]> wrote in message
news:[email protected]...
>I am having this problem and it is because of a GoTo in the
>error handler.
> The problem is that I need to stop the code on the error and go
> back to a
> specific point in the macro or I get stuck in a loop. Below is
> a brief piece
> of my code. Any help would be greatly appreciated.
>
> Sub abc()
>
> Dim.........
> Set Variable Values............
>
> Res1:
> Do While Range("PathList!A" & v_pathrow).Value <> Empty
>
> v_path = Range("PathList!A" & v_pathrow).Value
> On Error GoTo ErrorLg
> Workbooks.Open Filename:=v_path
> '*****Point of
> most errors
> Sheets("Cover Page").Select
> v_cc = Range("c16").Value
> v_currow = 1
> Do While Range("F" & v_currow).Value <> "Total Count"
> v_currow = v_currow + 1
> Loop '***Loop I get stuck in
>
>
> ActiveWindow.Close (False)
>
>
> v_pasterow = v_pasterow + 1
> v_pathrow = v_pathrow + 1
> Loop
> Rows(v_pasterow & ":" & v_pasterow + 1).Select
> Range("A18").Activate
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlUp
> Exit Sub
>
> ErrorLg:
> Range("ErrorLog!A" & v_errorrow).Value = v_path
> v_errorrow = v_errorrow + 1
> v_pathrow = v_pathrow + 1
> Rows(v_pasterow & ":" & v_pasterow).Select
> Selection.Delete Shift:=xlUp
> Err.Clear
> GoTo Res1
> --
> Thank You,
> Steve
>
>
> "Bill Schanks" wrote:
>
>> One thing I would suggest ... in the error handling routine do
>> you
>> have:
>>
>> Goto xxx
>>
>> OR
>>
>> Resume xxx
>>
>> If you have goto then the error routine will not catch a
>> second error,
>> as it is technically still in the error handling step.
>>
>>

Perfect!!! Thanks.
--
Thank You,
Steve


"Chip Pearson" wrote:

> Use Resume, not Goto, in your error handling block.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Steve D" <[email protected]> wrote in message
> news:[email protected]...
> >I am having this problem and it is because of a GoTo in the
> >error handler.
> > The problem is that I need to stop the code on the error and go
> > back to a
> > specific point in the macro or I get stuck in a loop. Below is
> > a brief piece
> > of my code. Any help would be greatly appreciated.
> >
> > Sub abc()
> >
> > Dim.........
> > Set Variable Values............
> >
> > Res1:
> > Do While Range("PathList!A" & v_pathrow).Value <> Empty
> >
> > v_path = Range("PathList!A" & v_pathrow).Value
> > On Error GoTo ErrorLg
> > Workbooks.Open Filename:=v_path
> > '*****Point of
> > most errors
> > Sheets("Cover Page").Select
> > v_cc = Range("c16").Value
> > v_currow = 1
> > Do While Range("F" & v_currow).Value <> "Total Count"
> > v_currow = v_currow + 1
> > Loop '***Loop I get stuck in
> >
> >
> > ActiveWindow.Close (False)
> >
> >
> > v_pasterow = v_pasterow + 1
> > v_pathrow = v_pathrow + 1
> > Loop
> > Rows(v_pasterow & ":" & v_pasterow + 1).Select
> > Range("A18").Activate
> > Application.CutCopyMode = False
> > Selection.Delete Shift:=xlUp
> > Exit Sub
> >
> > ErrorLg:
> > Range("ErrorLog!A" & v_errorrow).Value = v_path
> > v_errorrow = v_errorrow + 1
> > v_pathrow = v_pathrow + 1
> > Rows(v_pasterow & ":" & v_pasterow).Select
> > Selection.Delete Shift:=xlUp
> > Err.Clear
> > GoTo Res1
> > --
> > Thank You,
> > Steve
> >
> >
> > "Bill Schanks" wrote:
> >
> >> One thing I would suggest ... in the error handling routine do
> >> you
> >> have:
> >>
> >> Goto xxx
> >>
> >> OR
> >>
> >> Resume xxx
> >>
> >> If you have goto then the error routine will not catch a
> >> second error,
> >> as it is technically still in the error handling step.
> >>
> >>
>
>
>