VBA, array looping, subscript out of range

Question

I am getting subscript out of range error 9 on my 2nd lineof the For loop below. i = 7 (row number), and c1 = 59, c2=60 (column numbers). Why am I not getting subscript out of range here?

Should I be using 1 or 2 instead of c1 or c2? When I use 1 or 2 it is not picking up my columns correctly.

Dim c1 As Variant
Dim c2 As Variant
Dim arr() as Variant

Set rw = ws.Rows(6)
lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row 

c1 = Application.Match("Col 59)", rw, 0)
c2 = Application.Match("Col 60", rw, 0)

If Not IsError(c1) And Not IsError(c2) Then    'found both column headers?
    arr = ws.Range(rw.Cells(c1), rw.Cells(c2)).Resize(lastR).Value2
Else
    MsgBox "One or both required column headers not found!"
End If
    

'Loop to find Empty and Non empty fields
For i = 7 To UBound(arr) 'Row 7 is the row the data starts
    If (arr(i, c1) <> "" And arr(i, c2) = "") Or (arr(i, c2) <> "" And arr(i, c1) = "") Then        
        addToRange rngCopy, ws.Range("A" & i)
    End If
Next i

arr(1,1) would correspond to rw.Cells(c1) (so on Row 6 not Row 1, and column c1 not column 1) You can't use cell coordinates (row/column) if you didn't pick up the array starting at A1. — Tim Williams
– Tim Williams, Commented Nov 9, 2022 at 16:57
hi tim, i = 7 and c1 =59 , wouldnt this mean 7th row of the 59th column and then iterate downwards? — Jonnyboi
– Jonnyboi, Commented Nov 9, 2022 at 16:59
Your array values start from rw.Cells(c1), not from range A1. — Tim Williams
– Tim Williams, Commented Nov 9, 2022 at 16:59
If you pick up an array arr from (eg) C3:E6 then arr(1,1) is the value from C3 and arr(4,3) is the value from E6. — Tim Williams
– Tim Williams, Commented Nov 9, 2022 at 17:02

Tim Williams · Accepted Answer · 2022-11-09 18:13:40Z

2

Try this out - more code, but it saves you from needing to do any math on the array vs. range coordinates:

Sub Tester()
    Const HEADER_ROW As Long = 6
    Dim c1 As Variant, c2 As Variant, ws As Worksheet, rw As Range, lastR As Long
    Dim arr1 As Variant, arr2 As Variant, rng1 As Range, rng2 As Range, v1, v2
    Dim i As Long, rngCopy As Range
    
    Set ws = ActiveSheet 'or some other specific sheet
    Set rw = ws.Rows(HEADER_ROW)
    lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    
    c1 = Application.Match("Col 59)", rw, 0)
    c2 = Application.Match("Col 60", rw, 0)
    
    If Not IsError(c1) And Not IsError(c2) Then    'found both column headers?
        'define two ranges to pick up values from
        Set rng1 = ws.Range(rw.Cells(c1), ws.Cells(lastR, c1))
        Set rng2 = ws.Range(rw.Cells(c2), ws.Cells(lastR, c2))
        'fill the arrays
        arr1 = rng1.Value2
        arr2 = rng2.Value2
    Else
        MsgBox "One or both required column headers not found!"
        Exit Sub
    End If
        
    'Loop to find Empty and Non empty fields
    For i = 2 To UBound(arr1, 1) 'i=1 would be the column headers
        v1 = arr1(i, 1)         'read the two values to be compared
        v2 = arr2(i, 1)
        If (v1 <> "" And v2 = "") Or (v1 = "" And v2 <> "") Then
            addToRange rngCopy, ws.Cells(rng1.Cells(i).Row, "A")
        End If
    Next i
End Sub

edited Nov 9, 2022 at 18:13

answered Nov 9, 2022 at 17:23

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Jonnyboi Over a year ago

thanks Tim. getting an error on object required on line Set rng1 = ws.Range(rw.Cells(c1), ws.Cells(lastR, c1.Column))

Tim Williams Over a year ago

Sorry my bad - see edits above ws.Cells(lastR, c1) not ws.Cells(lastR, c1.Column) Same for the next line.

Collectives™ on Stack Overflow

VBA, array looping, subscript out of range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related