Restart Loop At Last Read Row

Question

I'm running a loop to read data from Excel and when I receive an error my script stops and gives me an vbAbortRetryIgnore option within a MsgBox. If I get an error, I want to be able to restart the loop at the beginning of that row that it stopped on with the "Retry" button or skip that row and go to the next row with the "Ignore" button.

My original loop is as follows:

Sub Main
    subGoToScreen "DELP", "********", "0000000001"
    Dim rw
    TITLE = "DELP Script"
    rw = 2
    Do While oExcelObject.CountA(oExcelSheet.Rows(rw)) > 0
        subDoWork oExcelSheet.Rows(rw)
        rw = rw + 1
    Loop
End Sub

Then if I receive an error like this Len(rw.Cells(1).Value) < 8 then if "Retry" is chosen, I'd like to go back to the first cell in that row restart the loop. I'm using this loop if "Retry" is chosen:

Sub MainRetry
    Dim rw
    TITLE = "DELP Script"
    rw = oExcelSheet.Rows(rw).Cells(1)

    Do While oExcelObject.CountA(oExcelSheet.Rows(rw)) > 0
        subDoWork oExcelSheet.Rows(rw)
        rw = rw + 1
    Loop
End Sub

It doesn't work and gives me a run time error. Does anyone know how to identify the current row I'm reading, remember that row and re-read the row or move to the next row? Currently, without the 2nd loop it picks back up reading the sheet at rw.Cells(2).Value.

Answer 1

This is how I got it working:

I defined a CurrentRow and a LastRow as follows:

CurrentRow          = rw.Cells(8).Row
LastRow             = CurrentRow - 1

Then I created two new sub-procedures, one to handle a retry and one to handle an ignore and set the starting point for the retry as rw = LastRow + 1 and the ignore as rw = CurrentRow + 1 while using the same loop

Do While oExcelObject.CountA(oExcelSheet.Rows(rw)) > 0
    subDoWork oExcelSheet.Rows(rw)
    rw = rw + 1
Loop