Error handing in For loop - Subscript out of range

Question

I am trying to loop through an String array, locate the row and column index using .Find method. If the method doesn't return anything, I want to capture the the error.

Below is my code. The problem I am having is that i keep getting the "Subscript out of range" error in Err: when variable i gets to the UBound(arrRDS) + 1 value which will stop the loop.

        On Error GoTo Err:
        For i = LBound(arrRDS) To UBound(arrRDS)
            
            sAcnt = Split(arrRDS(i), ";")(1)
            sAnl = Split(arrRDS(i), ";")(2)

            r = .Range("A:A").Find(What:=sAcnt, LookIn:=xlValues).Row
            c = .Range("6:6").Find(What:=sAnl, LookIn:=xlValues).Column
            ActiveSheet.Cells(r, c) = "x"

        Next

        Application.StatusBar = False
        
Err:
        Debug.Print arrRDS(i)
        Resume Next

Tim Williams Tim Williams · Accepted Answer · 2021-01-12T00:43:08

i will not reach UBound(arrRDS) + 1 inside the loop, so I would guess it's Debug.Print arrRDS(i) which is erroring, and that's because you have no Exit Sub/Function preventing your code from running right into your error handler (and i will be out of range at that point, since the loop only exits when i > UBound(arrRDS) ).

        On Error GoTo Err:
        For i = LBound(arrRDS) To UBound(arrRDS)
            
            sAcnt = Split(arrRDS(i), ";")(1)
            sAnl = Split(arrRDS(i), ";")(2)

            r = .Range("A:A").Find(What:=sAcnt, LookIn:=xlValues).Row
            c = .Range("6:6").Find(What:=sAnl, LookIn:=xlValues).Column
            ActiveSheet.Cells(r, c) = "x"

        Next

        Application.StatusBar = False
        Exit Sub                       '############
        
Err:
        Debug.Print arrRDS(i)
        Resume Next

Error handing in For loop - Subscript out of range

1 Answers