Pass range into an array and retrieve value

Question

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

possible duplicate of 2 Dimensional array from range

user2140173
– user2140173

2014-04-24 08:09:30 +00:00
Commented Apr 24, 2014 at 8:09 — user2140173
– user2140173, Commented Apr 24, 2014 at 8:09

Chris · Accepted Answer · 2014-04-24 03:32:00Z

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)

answered Apr 24, 2014 at 3:32

Chris

7983 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Zemens Over a year ago

+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)

Chris Over a year ago

@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.

David Zemens Over a year ago

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.

Chris Over a year ago

Cheers Dave, makes sense :)

Collectives™ on Stack Overflow

Pass range into an array and retrieve value

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related