1

I seem to have a fairly simple problem which is giving me grief. The following code throws a 'Subscript out of range' error. Therefore, I assume that the array is not being populated? Can anyone spot the glaring hole in what am I doing wrong...?

Dim p() As Variant
p = Sheet4.Range("G20:G29")

Sheet4.Select
Range("R2") = p(0)

ps G20:G29 contain strings, no blank cells

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
Chris
  • 737
  • 3
  • 16
  • 32
  • possible duplicate of [2 Dimensional array from range](http://stackoverflow.com/questions/18481330/2-dimensional-array-from-range) –  Apr 24 '14 at 08:09

1 Answers1

3

Hold it. Figured out the syntax. Forgot the Variant array was multidimensional

'First item
Range("R2") = p(1, 1)

'Second item
Range("R2") = p(2, 1)
Chris
  • 737
  • 3
  • 16
  • 32
  • 1
    +1 for solving on your own. Also the base of a range array is 1 instead of the typical 0. Use the `Locals` window in Excel to see the structure of your arrays :) Alternatively you could do `Range("R2") = Application.Transpose(p)(1)` – David Zemens Apr 24 '14 at 03:33
  • @David. Thanks for the reply. Was wondering if you knew the reason why the base was 1, not 0 in a multi dimensional array. Seems to juxtapose tradition. – Chris Apr 25 '14 at 22:00
  • Multi-dimensional arrays are not always Base 1. Range arrays are always base 1, whether they are single- or multi-dimensional. I am not sure off the top of my head but probably has something to do with a Range being an indexed collection of cells. Collections don't have an item `0`, so it would make sense to force the same logic on a range array. – David Zemens Apr 26 '14 at 01:46
  • Cheers Dave, makes sense :) – Chris Apr 26 '14 at 01:59