Arrays

[Ken Rock]

Hi,
I am trying to produce a sixteen element array from cells in a 4 by 4
section eg (A1:D4) of a larger 16 by 16 grid eg (A1:P16)
If I write

Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim xxx As Variant, yyy As Variant, zzz As Variant

With Worksheets("Sheet1")
Set rng1 = .Range("A1:P1")
Set rng2 = .Range("A1:A16")
Set rng3 = .Range("A1:D1, A2:D2, A3:D3, A4:D4")
End With
xxx = rng1.Value
yyy = rng2.Value
zzz = rng3.Value

then both xxx and yyy produce 16 element arrays. However, zzz produces
only a 4 element array ie the first row.
I can confirm that rng3 describes 16 cells by adding rng3.Select to the
code. The correct area is highlighted.
What else do I have to add to the code?
Thanks in advance,
Ken Rock

[paul.robinson@it-tallaght.ie]

Hi
Could you not use
Set rng3 = .Range("A1:D4")?

If you want something more flexible you could try
Set rng3 = .Range("A1").Resize(4, 4)

or even
Set rng3 = rng1.Cells(1,1).Resize(4,4)

I don't know why your code doesn't work. Possibly the .Value only picks
up the first area of the range - are the values in xxx those of A1 to
D1?

regards
Paul

[Bob Phillips]

Ranges and arrays are not the same thing, and don't behave identically. What
you see is the way that it works, it only picks up contiguous ranges. If you
want non-contiguous ranges in the array, you need to loop and get them all.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ken Rock" <k.rock@ntlworld.com> wrote in message
news:MrWef.4887$D03.365@newsfe5-gui.ntli.net...
> Hi,
> I am trying to produce a sixteen element array from cells in a 4 by 4
> section eg (A1:D4) of a larger 16 by 16 grid eg (A1:P16)
> If I write
>
> Dim rng1 As Range, rng2 As Range, rng3 As Range
> Dim xxx As Variant, yyy As Variant, zzz As Variant
>
> With Worksheets("Sheet1")
> Set rng1 = .Range("A1:P1")
> Set rng2 = .Range("A1:A16")
> Set rng3 = .Range("A1:D1, A2:D2, A3:D3, A4:D4")
> End With
> xxx = rng1.Value
> yyy = rng2.Value
> zzz = rng3.Value
>
> then both xxx and yyy produce 16 element arrays. However, zzz produces
> only a 4 element array ie the first row.
> I can confirm that rng3 describes 16 cells by adding rng3.Select to the
> code. The correct area is highlighted.
> What else do I have to add to the code?
> Thanks in advance,
> Ken Rock

[Ken Rock]

Ken Rock wrote:
> Hi,
> I am trying to produce a sixteen element array from cells in a 4 by 4
> section eg (A1:D4) of a larger 16 by 16 grid eg (A1:P16)
> If I write
>
> Dim rng1 As Range, rng2 As Range, rng3 As Range
> Dim xxx As Variant, yyy As Variant, zzz As Variant
>
> With Worksheets("Sheet1")
> Set rng1 = .Range("A1:P1")
> Set rng2 = .Range("A1:A16")
> Set rng3 = .Range("A1:D1, A2:D2, A3:D3, A4:D4")
> End With
> xxx = rng1.Value
> yyy = rng2.Value
> zzz = rng3.Value
>
> then both xxx and yyy produce 16 element arrays. However, zzz produces
> only a 4 element array ie the first row.
> I can confirm that rng3 describes 16 cells by adding rng3.Select to the
> code. The correct area is highlighted.
> What else do I have to add to the code?
> Thanks in advance,
> Ken Rock
Hi Gents,
Many thanks for your prompt responses to my query.
Paul,
Yes (A1:D4) does work and I shall use it. I don't know why I used the
four groups instead of (A1:D4) but it probably was during my vertical
learning curve on VBA arrays. I must have read an article somewhere on
loading data from a range into the array and used it as a starting point.
Bob,
I still haven't got my head around the properties of the Variant. I
thought an array had to have brackets to define it but if I use, say,
the expression zzz in the Add Watch facility, I see an array. Perhaps
there exists some easily digestible information somewhere on the web
about it.
Again, many thanks for your collective efforts.

Ken Rock