CODEWITHSUNDEEP
arraysvbaexcel
jamheadart
jamheadart

Reputation: 5303

VBA - cycle through 2D Array defined by a range

OK so I found a sort of fix for this problem on here but it wasn't in VBA so I just need the right syntax to pluck info from the array.

I've defined an array from a range on a sheet. I've figured out that this isn't actually a 1D Array as I'd first thought even though the 2nd dimension parameter is just 1.

Now I'm just trying to cycle through the array to help me get my head round how they work and I get a subscript out of range error.

Dim arr1 As Variant
Dim e As Variant

    arr1 = Array(ActiveSheet.Range("A1:A4"))

For e = LBound(arr1) To UBound(arr1)
    MsgBox (arr1(e, 1))
Next e

How can I fix the MsgBox (arr1(e,1)) line?

Upvotes: 1

Views: 499

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

There were a couple problems:

  1. You do not need the Array(..) when assigning a range to an array
  2. You should specify the dimension on the Ubound and Lbound

So here:

Dim arr1 As Variant
Dim e As Long

    arr1 = ActiveSheet.Range("A1:A4").Value

For e = LBound(arr1,1) To UBound(arr1,1)
    MsgBox arr1(e, 1)
Next e

Upvotes: 2

user3598756
user3598756

Reputation: 29421

being a real 1-D array you could go this way:

Option Explicit

Sub main()
    Dim arr1 As Variant
    Dim e As Long

    arr1 = Application.Transpose(ActiveSheet.Range("A1:A4").value) '<--| transposing a 1-column range you get a 1-row range that fits in an actual 1-D array        
    For e = LBound(arr1) To UBound(arr1) <--| no need to specify the column index
        MsgBox arr1(e)
    Next e
End Sub

as for iterating through the array, you may want to use the For Each syntax:

Option Explicit

Sub main()
    Dim arr1 As Variant, elem As Variant

    arr1 = Application.Transpose(ActiveSheet.Range("A1:A4").value)            
    For Each elem In arr1
        MsgBox elem
    Next elem    
End Sub

Upvotes: 2

Related Questions