Dynamic Array problem

[Werner]

Hi,

I want to put data in a dynamic Array with a looping. If I use a variable to change the position in my vurtual dynamic array,it gives me an error saying I am out of range. The solution I've found and tried in other posts is to redim the array, but by doing so I loose all the other datas from the previous positions (other than the actual position redimed). SO is there a way make my dynamic array works?

Thanks!

JJD

Sub Age_moyenne_ponderee()

'Je déclare es tableaux et mes variables que j'aurai besoin pour incrémenter.
Dim tabdynamique() As Double
Dim I As Long
Dim J As Variant
J = 0
Dim Z As Integer

' Age looping. Le principe est que la boucle effectue le calcul sur la ligne la ligne si _
elle voit qu'elle n'est pas vide et change de ligne une fois la suppression _
de la ligne effectuée.

For I = 12 To 65536

If Worksheets("Formulaire").Range("A" & I).Value <> "" Then

J = J + 1
ReDim tabdynamique(J)
tabdynamique(J) = (((Worksheets("Formulaire").Range("E" & I).Value) / (Worksheets("Formulaire").Range("R5").Value)) * (Worksheets("Formulaire").Range("H" & I).Value))

Sheets("Formulaire").Range("AF" & I).Value = tabdynamique(J)
Else
Exit For
End If
Next I

End Sub

[Ron Coderre]

Try REDIM PRESERVE

From VB Help:
Preserve (Optional Keyword) used to preserve the data in an existing array when you change the size of the last dimension.

Does that help?

Regards,
Ron

[Werner]

Hi Ron,

Thanks for your try but unfortunetaly, the values are not retained in the array even with redim preserve.

Any other idea?

Werner

[tkstock]

Lets see your new code (please enclose it in 'code' tags)

Thanks

[Werner]

Hi,

Here's my new code. It says I am out of range at line:
ReDim Preserve tabdynamique(UBound(tabdynamique) + 1)

Note that I use Excel 97. Could it be part of an unsupported feature for this version of VBA?

Regards,

Werner

Sub Age_moyenne_ponderee2()

Dim tabdynamique() As Double

Dim I As Long

Let lstRw = Sheets("Formulaire").Range("a65536").End(xlUp).Row

For I = 16 To lstRw

ReDim Preserve tabdynamique(UBound(tabdynamique) + 1)
tabdynamique(UBound(tabdynamique)) = (((Worksheets("Formulaire").Range("E" & I).Value) / (Worksheets("Formulaire").Range("R5").Value)) * (Worksheets("Formulaire").Range("H" & I).Value))

Sheets("Formulaire").Range("AF" & I).Value = tabdynamique(UBound(tabdynamique))

Next I

End Sub

[Norie]

The problem is the first time you try and redim the array has no dimension.

So this will fail

Please Login or Register  to view this content.

Try this

Please Login or Register  to view this content.

[Werner]

Thx a lot! It works!

Have a good day!

Werner