arrray code in Excel keeps generating error

the_current_thing 0
Viewed 39

Where am I going wrong below? Code keeps throwing Subscript out of range error:

Sub test()

Dim arr As Variant    
arr = Range("a1", "a6").Value  
a = LBound(arr)
b = UBound(arr)

For i = a To b
    MsgBox arr(i)
Next
    
End Sub
vba excel arrays
1 Answers

Arrays pulled from ranges in excel are always two-dimensional which is a pain (compared to say google sheets appscripts where it's just arrays within arrays). A fabulous resource of Excel Arrays is from the late Chip Pearson who posted this site years ago.

Anyway, as Scott said... you just have to do two nested level...

Sub test()
Dim arr As Variant, i As Long, j As Long
arr = Range("A1", "A6").Value
    
    For i = LBound(arr) To UBound(arr)
        For j = LBound(arr, 2) To UBound(arr, 2)
        
            MsgBox arr(i, j)
            
        Next j
    Next i
    
End Sub
pgSystemTester0
Related