I have a piece of code that loops
thru my rows
in excel and find the first character
of a string
in a particular column
. While that works perfectly with i = i + 1
, when the first condition
passed, it will then execute another action. Inside the action, sometime i have errors
happening, thus i put On error Handlers
so that i can move on to the next row
. Here is the code that i have.
On Error GoTo errHandler
Check:
While Cells(7 + i, 1).Value <> ""
firstChar = Left(Cells(7 + i, 6).Value, 1)
If firstChar = "A" Then
.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm02"
.findById("wnd[0]").sendVKey 0
.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = ""
.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = Cells(7 + i, 2)
.findById("wnd[0]").sendVKey 0
.findById("wnd[1]/tbar[0]/btn[0]").press
.findById("wnd[1]/usr/ctxtRMMG1-LGTYP").Text = "AN1"
.findById("wnd[1]/usr/ctxtRMMG1-LGTYP").SetFocus
.findById("wnd[1]/usr/ctxtRMMG1-LGTYP").caretPosition = 3
.findById("wnd[1]/tbar[0]/btn[0]").press
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP21/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2731/ctxtMLGN-PLKPT").Text = "AN1"
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP21/ssubTABFRA1:SAPLMGMM:2000/subSUB3:SAPLMGD1:2733/ctxtMLGN-LTKZA").Text = "AN1"
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP21/ssubTABFRA1:SAPLMGMM:2000/subSUB3:SAPLMGD1:2733/ctxtMLGN-LTKZE").Text = "AN1"
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP21/ssubTABFRA1:SAPLMGMM:2000/subSUB3:SAPLMGD1:2733/ctxtMLGN-LTKZE").SetFocus
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP21/ssubTABFRA1:SAPLMGMM:2000/subSUB3:SAPLMGD1:2733/ctxtMLGN-LTKZE").caretPosition = 3
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP22").Select
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP22/ssubTABFRA1:SAPLMGMM:2000/subSUB3:SAPLMGD1:2734/ctxtMLGT-LGPLA").Text = Cells(7 + i, 6)
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP22/ssubTABFRA1:SAPLMGMM:2000/subSUB3:SAPLMGD1:2734/ctxtMLGT-LGPLA").SetFocus
.findById("wnd[0]/usr/tabsTABSPR1/tabpSP22/ssubTABFRA1:SAPLMGMM:2000/subSUB3:SAPLMGD1:2734/ctxtMLGT-LGPLA").caretPosition = 8
.findById("wnd[0]/tbar[0]/btn[11]").press
.findById("wnd[0]/tbar[0]/okcd").Text = "/N"
.findById("wnd[0]").sendVKey 0
ElseIf firstChar = "B" Then
#another action
ElseIf firstChar = "C" Then
#another action
Else:
End If
i = i + 1
errHandler:
Cells(7 + i, 9).Value = "Error"
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[12]").press
session.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
GoTo Check
Basically, if there are no errors in between the actions, the loop
works fine and go thru to another rows
, if there is an error
, errHandler
's action will be performed but it will goes back to check:
and perform on the same row
again. Also just to let everyone know, the firstChar
that im searching for does not exist in every row
, it can be on row 10
and the next one will be 55th
.
Also, i tested by adding i = i + 1
to errHandler
and yes the first error
was successfully avoided by going to the next row
. However, if it meets another error
, the same thing happens again, executing on the same row
. Does it have anything to do with finding the char
'A'
,'B'
,'C'
?
An error handler can only handle one error at a time. Your handler was not reset so it is encountering the second error while still handling the first.
The only way to reset the handler is by executing one of the following:
Resume
Exit Sub
Exit Function
Exit Property
On Error Goto -1
Note that Err.Clear
and On Error Goto 0
will clear the error number but they will not reset the handier. Also, On Error Resume Next
is not the same as Resume
(above) so it will not reset you handler either.
Your solution: replace GoTo Check
with Resume Check
Using Resume Check
will reset the handler and send execution to the Label
just like your GoTo
. This will also fix the infinite loop your GoTo
appears to cause. Presumably that code block should only be executed after raising an error so you may want to use Exit Sub
or Exit Function
above errHandler:
.