Array from Range in Excel VBA

Kevin Boyd picture Kevin Boyd · Oct 11, 2009 · Viewed 67.7k times · Source

Well I've been struggling with the little bit of code and can't seem to get around it ... I'm trying to get an array from a range of cells, the array however is showing up to be 1 element wide.
Well here's the code:

Dim item As Variant
MsgBox Range("D19:H19").Count    
item = Range("D19:H19").Value
MsgBox LBound(item) & " " & UBound(item)   

as per my understanding item should contain a 2D array... however I'm getting the following result 1st MsgBox prints 5 2nd MsgBox prints 1 1

What's going wrong?

Answer

imfrancisd picture imfrancisd · Oct 11, 2009

The problem is in LBound and UBound

jtolle was correct about the LBound and UBound.

LBound(item, 2)

UBound(item, 2)

However, item must not be dimmed as an array (you'll get an error).

I think this is what you want

Dim item As Variant
MsgBox Range("D19:H19").Count
item = Range("D19:H19").Value

MsgBox LBound(item, 2) & " " & UBound(item, 2)

For i = LBound(item, 2) To UBound(item, 2)
  MsgBox item(1, i)
Next