Search code examples
vbasap-gui

VBA cannot loop through rows


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'?


Solution

  • 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:.