ReDim Preserve on 2D Array not working
 

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

ReDim Preserve on 2D Array not working
 

"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

"bony_tony" wrote in message
...
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

ReDim Preserve on 2D Array not working
 

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

"Keith R" wrote in message
...
"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

"bony_tony" wrote in message
...
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

ReDim Preserve on 2D Array not working
 

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


"bony_tony" wrote:

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

ReDim Preserve on 2D Array not working
 

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

Alan Beban

bony_tony wrote:
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

ReDim Preserve on 2D Array not working
 

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)


"bony_tony" wrote in message
...
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