Redim preserve does not work for me

[Dan]

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

 

[Nigel]

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.

 

[Dan]

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

 

[Jacob Skaria]

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

 

[Stefi]

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: