I am trying to understand hoe redim preserve works, but I keep getting run
time error 9

Any idea what is wrong with my redim preserve.
Thank you.
Dan

For example in the bellow:

Public MyReportArray() As Variant
Sub testpreserve()
ReDim MyReportArray(5, 5)
MyReportArray = Range("A5:E5")
ReDim Preserve MyReportArray(6, 6)
MyReportArray = Range("A6:F5")
End Sub

From Excel help file....

If you use Preserve, you can only resize the last array dimension and you
can't change the number of dimensions at all.

--

Regards,
Nigel




"Dan" wrote in message
...
I am trying to understand hoe redim preserve works, but I keep getting run
time error 9

Any idea what is wrong with my redim preserve.
Thank you.
Dan

For example in the bellow:

Public MyReportArray() As Variant
Sub testpreserve()
ReDim MyReportArray(5, 5)
MyReportArray = Range("A5:E5")
ReDim Preserve MyReportArray(6, 6)
MyReportArray = Range("A6:F5")
End Sub

????
I am not changing the number of dimmensions - always 2

"Nigel" wrote:

From Excel help file....

If you use Preserve, you can only resize the last array dimension and you
can't change the number of dimensions at all.

--

Regards,
Nigel




"Dan" wrote in message
...
I am trying to understand hoe redim preserve works, but I keep getting run
time error 9

Any idea what is wrong with my redim preserve.
Thank you.
Dan

For example in the bellow:

Public MyReportArray() As Variant
Sub testpreserve()
ReDim MyReportArray(5, 5)
MyReportArray = Range("A5:E5")
ReDim Preserve MyReportArray(6, 6)
MyReportArray = Range("A6:F5")
End Sub

What Nigel has mentioned is

Dim MyReportArray
ReDim MyReportArray(5, 5)

You can ReDimension to MyReportArray(5,6) or to MyReportArray(5,100)
but cannot
ReDimension to MyReportArray(6,5)

If this post helps click Yes
----------------
Jacob Skaria

"Dan" wrote:

????
I am not changing the number of dimmensions - always 2

"Nigel" wrote:

From Excel help file....

If you use Preserve, you can only resize the last array dimension and you
can't change the number of dimensions at all.

--

Regards,
Nigel




"Dan" wrote in message
...
I am trying to understand hoe redim preserve works, but I keep getting run
time error 9

Any idea what is wrong with my redim preserve.
Thank you.
Dan

For example in the bellow:

Public MyReportArray() As Variant
Sub testpreserve()
ReDim MyReportArray(5, 5)
MyReportArray = Range("A5:E5")
ReDim Preserve MyReportArray(6, 6)
MyReportArray = Range("A6:F5")
End Sub

If you declare array in tthis way: MyReportArray = Range("A6:F5") then you
don't need Redim at all.

Public MyReportArray() As Variant
Sub testpreserve()
MyReportArray = Range("A5:E5")
MyReportArray = Range("A6:F5")
End Sub

worked for me and MyReportArray contained cell contents A6:F5.

Regards,
Stefi


€žDan€� ezt Ã*rta:

????
I am not changing the number of dimmensions - always 2

"Nigel" wrote:

From Excel help file....

If you use Preserve, you can only resize the last array dimension and you
can't change the number of dimensions at all.

--

Regards,
Nigel




"Dan" wrote in message
...
I am trying to understand hoe redim preserve works, but I keep getting run
time error 9

Any idea what is wrong with my redim preserve.
Thank you.
Dan

For example in the bellow:

Public MyReportArray() As Variant
Sub testpreserve()
ReDim MyReportArray(5, 5)
MyReportArray = Range("A5:E5")
ReDim Preserve MyReportArray(6, 6)
MyReportArray = Range("A6:F5")
End Sub