Dynamic Array problem
 

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


--
Werner
------------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=379765

Dynamic Array problem
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=379765

Dynamic Array problem
 

Hi Ron,

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

Any other idea?

Werne

--
Werne
-----------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...fo&userid=2430
View this thread: http://www.excelforum.com/showthread.php?threadid=37976

Dynamic Array problem
 

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

Thanks


--
tkstock


------------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...o&userid=14443
View this thread: http://www.excelforum.com/showthread...hreadid=379765

Dynamic Array problem
 

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




--
Werner
------------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=379765

Dynamic Array problem
 

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

So this will fail

Code:
--------------------

UBound(tabdynamique)
--------------------


Try this

Code:
--------------------

ReDim Preserve tabdynamique(I - 15)
--------------------


--
Norie
------------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362
View this thread: http://www.excelforum.com/showthread...hreadid=379765

Dynamic Array problem
 

Thx a lot! It works!

Have a good day!

Werne

--
Werne
-----------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...fo&userid=2430
View this thread: http://www.excelforum.com/showthread.php?threadid=37976