Search code examples
vbaexceloffice-2007

Array from Range in Excel VBA

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?

Solution

  • 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