How to solve VBA error Subscript out of range

Question

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!

What is the value of j when it fails? Basically the error is saying the index position doesn't exist in your array. — QHarr
– QHarr, Commented Sep 6, 2018 at 8:25
j is 1, Counter is 2 when it fails. Actually cell A2 matches one of the cell values in column F. — open0121
– open0121, Commented Sep 6, 2018 at 8:27
You have to redim rowArr before trying to access it as an array. — GSerg
– GSerg, Commented Sep 6, 2018 at 8:27
@open0121 the dynamic size means that initially the array does not have any size. — Shadow
– Shadow, Commented Sep 6, 2018 at 8:38

Olly · Accepted Answer · 2018-09-06 08:29:34Z

2

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

answered Sep 6, 2018 at 8:29

Olly

7,9021 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

open0121 Over a year ago

Now the same error goes to line 'With ws.Range("A" & rowArr(i) & ":B" & rowArr(i))'. What would be the problem this time?

Olly Over a year ago

j exceeds the upper bound of array rowArr. You can change this by changing how you increment j in your initial loop, or you can loop i from 1 to j-1

open0121 Over a year ago

I also find another problem that the Application.Match only find the match value for the first item in the searching range 'ws.Columns(6)', while the other matches are missing... Do you have any idea how to solve the problem?

open0121 Over a year ago

thanks! I kind of understand your point, but don't exactly know how to correct the code (I'm new to Macros)

open0121 Over a year ago

I found the solution! Thanks to your tips Olly!

|

Collectives™ on Stack Overflow

How to solve VBA error Subscript out of range

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related