Can't handle error more than once - perplexing !

  • Thread starter Thread starter rahul_chatterjee
  • Start date Start date
R

rahul_chatterjee

Hi,

A simplistic version of my problem is outlined below.

Suppose I have Sheet1 with data -

Month Value
January 2
February 6
March 7

And Sheet2 contains data -

Month Value
January
March
'February is missing

Now the code needs to take up a month from Sheet1, find a match in
Sheet2 and plug in Sheet2 for the month its corresponding value from
Sheet1. If it does not find the month in Sheet2 (in this case
February) then the error handler should show the error in a MsgBox and
note the missing month in Sheet3. The code to do this is given below -

'*********************************************
Sub SalesUpdate()

Dim i
Dim store
Dim errornumber
i = 2
errornumber = 1

Do While i < 13
Sheets("Sheet1").Select
store = Range("A" & i) 'Reads the month in Sheet1
Sheets("Sheet2").Select
'Selects column A in Sheet2 and then searches for the month in
Sheet1
Columns("A:A").Select
Selection.Find(What:=store,After:=ActiveCell,LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByColumns,SearchDirection:=xlNext,
MatchCase:=False).Activate

On Error GoTo ErrTrap

'If the month in Sheet1 is found in Sheet2 then the Value for
'the month in Sheet1 is written to Sheet2 corresponding
'to the month in consideration

ActiveCell.Offset(0, 1).Value = "=Sheet1!R" & i & "C2"
GoTo CountIncrement

'If the month is not found in Sheet2, show msgbox and note
'the month in Sheet3

ErrTrap:
MsgBox "Month " & store & " not found. Error number " &
errornumber
Sheets("Sheet3").Range("A" & errornumber).Value =
Sheets("Sheet1").Range("A" & i)
errornumber = errornumber + 1

CountIncrement:
i = i + 1
Loop

End Sub
'*********************************************

The problem I am facing is that this code works fine for the first
instance of an error. So for the first error, month February shall be
identified and written to Sheet3. But when the next instance of a
missing month is encountered, the code gives a run time error in the
Find statement and the error is not handled by the On Error statement.
I am new to Excel VBA coding and am finding this perplexing.

Thank you for your inputs.

Rahul
 
Rahul,

You need to use a Resume statement in your error handling block in
order to "reset" the error handler. Otherwise, you are still in
"error mode" and cannot trap subsequent errors.

To resume execution in normal mode at the error trap, modify your
code as shown below.


ErrTrap:
If Err.Number <> 0 Then
Resume ResHere:
End If
ResHere:
MsgBox "Month " & store & " not found. Error number " &
errornumber
Sheets("Sheet3").Range("A" & errornumber).Value = _
Sheets("Sheet1").Range("A" & i)
errornumber = errornumber + 1
CountIncrement:
i = i + 1
Loop


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Sub SalesUpdate()

Dim i
Dim store
Dim errornumber
i = 2
errornumber = 1

Do While i < 13
Sheets("Sheet1").Select
store = Range("A" & i) 'Reads the month in Sheet1
Sheets("Sheet2").Select
'Selects column A in Sheet2 and then searches for the month in
Sheet1
Columns("A:A").Select
Selection.Find(What:=store,After:=ActiveCell,LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByColumns,SearchDirection:=xlNext,
MatchCase:=False).Activate

On Error GoTo ErrTrap

'If the month in Sheet1 is found in Sheet2 then the Value for
'the month in Sheet1 is written to Sheet2 corresponding
'to the month in consideration

ActiveCell.Offset(0, 1).Value = "=Sheet1!R" & i & "C2"


'If the month is not found in Sheet2, show msgbox and note
'the month in Sheet3

CountIncrement:
i = i + 1
Loop

Exit Sub
ErrTrap:
MsgBox "Month " & store & " not found. Error number " &
errornumber
Sheets("Sheet3").Range("A" & errornumber).Value =
Sheets("Sheet1").Range("A" & i)
errornumber = errornumber + 1

Resume CountIncrement
End Sub

There are much easier ways to do this, but this might show you why your
error handler doesn't work the second time. You need a Resume statement to
exit errorhandling mode - since you don't have one, you when you hit the
second error, you are still considered to be in your error handler - so
Excel gives up at that point (an error in the error handler - not
acceptable).
 
Back
Top