excel vba: subscript out of range

Question

the vba code is:

    Sub D()
    Dim a As String
    Dim wb As Workbook
      Dim file As Variant

       Dim arr1() As Variant
      Dim arr2() As Variant
      Dim arr3() As Variant
      Dim arr() As Variant
      Dim arr4() As Variant
       Dim arr5() As Variant
       Dim arr6() As Variant

       Dim t As Integer



            ActiveWorkbook.Activate
            ActiveSheet.Activate
            arr4 = Range("J2:J256")
            arr5 = Range("K2:K256")
            arr6 = Range("L2:L256")


         ActiveSheet.Activate


            For Row = 1 To UBound(arr4, 1)
                If arr4(Row, 10) = "IS" And arr5(Row, 11) = "IS" And arr6(Row, 12) = "IS" Then
                Cells(Row + 1, 13) = "UPDATE AB SET S=" & Cells(Row + 1, 6) & "WHERE C=" & Cells(Row + 1, 3) & ";"
                End If
            Next Row
    End Sub

I am getting error as Subscript Out of range at arr4(Row,10)= when debugged.Can u help in rectifying the error so that the code may be able to function correctly.

Ambie · Accepted Answer · 2016-03-26 13:25:07Z

I think the problem lies in your understanding of the array. You are using absolute cell references for your array (which is relative). The first index of an array, ie (1, 1), references the first cell in your range, so for Range("K2:K256") arr(1, 1) will be referencing the value of cell "K2", arr(10, 1) will be referencing the value of cell "K11", etc.

As LMM9790 points out, if you wanted to keep your code structure as is then it could simply be written as:

        If arr4(Row, 1) = "IS" And arr5(Row, 1) = "IS" And arr6(Row, 1) = "IS" Then
            Cells(Row + 1, 13) = "UPDATE AB SET S=" & Cells(Row + 1, 6) & "WHERE C=" & Cells(Row + 1, 3) & ";"
        End If

However, I'd have to ask why you need so many arrays, one for each column? Given that arr4, 5 and 6 all have the same row dimension, you could simply have one array that contains all of the columns. Moreover, you could have one array for the entire dataset, amend the applicable value, then rewrite the array to the Worksheet.

Elsewhere the code is a little odd. Is there a reason, for example, that you would activate an active sheet and book? You also have several unused variables - are you intending to use these later?

Your whole code could be simplified to this:

Sub D()
    Dim ws as Worksheet
    Dim r As Integer
    Dim v As Variant

    Set ws = ActiveWorkbook.Worksheets("Sheet1") 'name as appropriate
    v = ws.Range("C2:M256").Value2

    For r = 1 To UBound(v, 1)
        If r < Ubound(v, 1) then
            If v(r, 8) = "IS" And v(r, 9) = "IS" And v(r, 10) = "IS" Then
                 v(r + 1, 11) = "UPDATE AB SET S=" & v(r + 1, 4) & _
                                " WHERE C=" & v(r + 1, 1) & ";"
            End If
        End If
    Next

    '...

    ws.Range(("C2:M256").Value = v
End Sub

LMM9790 · Accepted Answer · 2016-03-25 12:19:56Z

Regarding the out of range error, since arr4, arr5 and arr6 only contain one column you cannot access for example the 10th column of them (which is done in your code by arr4(Row, 10). Does you code work as wanted if you use the following?

Sub D()
      Dim a As String
      Dim wb As Workbook
      Dim file As Variant
      Dim Row As Integer
      Dim arr1() As Variant
      Dim arr2() As Variant
      Dim arr3() As Variant
      Dim arr() As Variant
      Dim arr4() As Variant
      Dim arr5() As Variant
      Dim arr6() As Variant
      Dim t As Integer

            ActiveWorkbook.Activate
            ActiveSheet.Activate
            arr4 = Range("J2:J256")
            arr5 = Range("K2:K256")
            arr6 = Range("L2:L256")

            For Row = 1 To UBound(arr4, 1)
                If arr4(Row, 1) = "IS" And arr5(Row, 1) = "IS" And arr6(Row, 1) = "IS" Then
                     Cells(Row + 1, 13) = "UPDATE AB SET S=" & Cells(Row + 1, 6) & "WHERE C=" & Cells(Row + 1, 3) & ";"
                End If
            Next Row
    End Sub

Collectives™ on Stack Overflow

excel vba: subscript out of range

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related