ReDim Preserve on 2D Array not working

[bony_tony]

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

 

[Keith R]

"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

 

[Keith R]

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

 

[DomThePom]

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

 

[Alan Beban]

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

Alan Beban

 

[Chip Pearson]

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.


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