Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic array problem | Excel Programming | |||
Dynamic 2-Dim Array | Excel Programming | |||
Dynamic 2D Array | Excel Programming | |||
Dynamic Array | Excel Programming | |||
see if dynamic array used | Excel Programming |