CODEWITHSUNDEEP
arraysvbaexcel
user3737057
user3737057

Reputation: 121

Get i'th element of an array read from an Excel range

I have created an array using:

Dim PodList() As Variant
Dim i As Long
PodList = Range("A5:A19")

For i = LBound(PodList) To UBound(PodList)
    Range("B4").Value = PodList(i)
Next

My line in the loop does not work. I am trying to print/paste the ith element of PodList into cell B4, but I am getting "subscript out of range".

Upvotes: 1

Views: 1163

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

This

PodList = Range("A5:A19")

returns a two-dimensional array. Reading a range into a Variant always returns a two-dimensional array (except when you're reading a single cell, in which case it's just one value, not an array).

Your 2D array has dimensions (15,1) i.e. 15 rows and 1 column. But by asking for PodList(i) i.e. with only one index or subscript, you treated it like a one-dimensional array, which it isn't, hence the "subscript out of range" error.

You want to iterate through the rows of the (one) column, referring to both subscripts of the 2D array, like this:

For i = LBound(PodList,1) To UBound(PodList,1)
    Range("B4").Value = PodList(i,1)
Next

LBound(PodList,1) and UBound(PodList,1) tell you the bounds of the first dimension (and will return 1 and 15, respectively).

Upvotes: 5

Related Questions