ReDim Preserve on 2D Array not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bony_tony - 07 Dec 2007 16:06 GMT

Hi,
I'm new to arrays.

I have attempted to create a 2D array for the following data in my
sheet. ("Joe" is in cell C3)

Name    From    To
Joe    A    E
Pritesh    F    Q
Richard    R    Z

I have the following, but after it tries to ReDim for the second time,
I get the error "Subscript out of range" I'm not sure what I'm doing
wrong.

Here's my code;

For i = 3 To Range("C65536").End(xlUp).Row
ReDim Preserve Split(1 To i - 2, 1 To 3)
Split(i - 2, 1) = Cells(i, 3)
Split(i - 2, 2) = Cells(i, 4)
Split(i - 2, 3) = Cells(i, 5)
Next i

Am I making an obvious mistake?

Cheers
Tony

Reply to this Message

Keith R - 07 Dec 2007 16:11 GMT

"If you use the Preserve keyword, you can resize only the last array
dimension and you can't change the number of dimensions at all. For example,
if your array has only one dimension, you can resize that dimension because
it is the last and only dimension. However, if your array has two or more
dimensions, you can change the size of only the last dimension and still
preserve the contents of the array."

HTH,
Keith

> Hi,
> I'm new to arrays.
[quoted text clipped - 24 lines]
> Cheers
> Tony

Reply to this Message

Keith R - 07 Dec 2007 16:24 GMT

One other thought; redimming within your loop is 'expensive'. Consider
identifying the size of your target range first, redim the array just once
to make it that size, then loop to fill it. (Alternatively, dim your array
as a variant, and just assign the range to it).

Best,
Keith

> "If you use the Preserve keyword, you can resize only the last array
> dimension and you can't change the number of dimensions at all. For
[quoted text clipped - 34 lines]
>> Cheers
>> Tony

Reply to this Message

DomThePom - 07 Dec 2007 16:46 GMT

a couple more thoughts:

1. be careful of using key vba words as variable names - can only lead to
trouble! (the split function splits delimited data into its components)

2. Use current region to define your range - see code that follows

Sub fillMyArray()
Dim rng As Range
Dim var As Variant

'define tsble to be input to array
Set rng = Sheets("Sheet1").Range("C2").CurrentRegion
'slice off the top row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
var = rng

'do what you want with your data

Set rng = Nothing
End Sub

> Hi,
> I'm new to arrays.
[quoted text clipped - 24 lines]
> Cheers
> Tony

Reply to this Message

Alan Beban - 07 Dec 2007 17:56 GMT

You don't say what "Split" is before you try to ReDim the first time.

Alan Beban

> Hi,
> I'm new to arrays.
[quoted text clipped - 24 lines]
> Cheers
> Tony

Reply to this Message

Chip Pearson - 09 Dec 2007 15:21 GMT

I would not use Split as a variable name. Split is the name of a VB function
that splits a string in to substrings based on a delimiter character. Choose
something other than Split.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Hi,
> I'm new to arrays.
[quoted text clipped - 24 lines]
> Cheers
> Tony

Reply to this Message