I have an function that needs to have a dynamic array..
Sub Array Test
Dim myArray() as String
Dim RowData as Long
Dim Counter as Integer
RowData = 1
Do While Cells(RowData, 1).VAlue <> ""
Redim Preserve myArray(RowData, 9)
For Counter = 0 to 9
myArray(RowData, Counter) = Cells(RowData, Counter + 1)
Next Counter
RowData = RowData + 1
Loop
End Sub
Excel doesn't let this work as you can only change the second part of the array when you preserve Redim it.
I see 3 possible solutions, but am unsure which one would be the best...
#1) Change the order so it would be [ReDim Preserve myArray(9, RowData)]
#2) Determine the max number that it could be before including the data, thus excluding it from needing the Preserve keyword.
#3) Create 2 similar arrays and swap the data between the two as you Redim to make them larger, but without the necessity of the Preserve.
But as this is more of a side project I'm working on I thought I'd ask here, rather then try each out to see the time difference in them.
Any help would be appreciated. Thanks,
John
Bookmarks