CODEWITHSUNDEEP
vbaexcel
open0121
open0121

Reputation: 105

How to solve VBA error Subscript out of range

I tried to find which rows have cell value in Column A matches one of the cell values in another column in the same worksheet.

I got error

Subscript out of range

and don't know why this occurs.

Here is the code:

Sub test()  
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim FoundCell As Range
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    Dim colA As Variant, rowArr() As Variant, ub As Long, Counter As Long, j As Long, i As Long, Num As Variant
    colA = ws.UsedRange.Columns("A").Value2
    ub = UBound(colA)

    j = 1
    For Counter = 1 To ub
        Num = ws.Cells(Counter, 1).Value
        If Not IsError(Application.Match(Num, ws.Columns(6), 0)) Then
            rowArr(j) = Counter
            j = j + 1
        End If
    Next Counter

    ReDim Preserve rowArr(1 To j - 1)


    For i = 1 To j
        With ws.Range("A" & rowArr(i) & ":B" & rowArr(i))
            .Font.Size = 12
            .Font.Shadow = True
        End With
    Next
End Sub

The error occurs in the line rowArr(j) = Counter. Anyone knows what's the problem?

Thanks in advance!

Upvotes: 0

Views: 6545

Answers (1)

Olly
Olly

Reputation: 7891

You need to redim your array before you populate values:

j = 1
For Counter = 1 To ub
    Num = ws.Cells(Counter, 1).Value
    If Not IsError(Application.Match(Num, ws.Columns(6), 0)) Then
        ReDim Preserve rowArr(1 To j)
        rowArr(j) = Counter
        j = j + 1
    End If
Next Counter

Upvotes: 2

Related Questions