PDA

View Full Version : Solved: Array help?



TrippyTom
08-29-2008, 04:30 PM
I'm at a complate loss when it comes to understanding 2 dimensional arrays.

disclaimer: I "dumbed down" the attached worksheet in order to explain my question and to hide sensitive data. The process is still the same, even though the calculations are much more complex in the actual file.

Situation:
The attached worksheet has ONE ROW of data that gets updated every time cell B1 changes. I want to populate an array (1000 rows down by 5 columns across) by incrementing B1 from 1 - 1000.
I got the following code from a book: "Microsoft Excel 2000 Power Programming with VBA" by John Walkenbach (pp 288-289 "Borrowing arrays to fill ranges faster"). But I'm having trouble understanding how to populate the same range (B4:F4) 1000 times to put it into an array & then paste it back as a large range all at once.


Sub ArrayFillRange()
'Fill a range by transferring an array
Dim TempArray() As Integer
Dim TheRange As Range

'Get the dimensions
cellsdown = 1000
cellsacross = 5

'Record starting time
starttime = Timer

'Redimension temporary array
ReDim TempArray(1 To cellsdown, 1 To cellsacross)

'Set worksheet range
Set TheRange = ActiveCell.Range(Cells(1, 1), _
Cells(cellsdown, cellsacross))

'Fill the tempoary array
currval = 0
Application.ScreenUpdating = False
For i = 1 To cellsdown
For j = 1 To cellsacross
TempArray(i, j) = currval + 1
currval = currval + 1
Next j
Next i

'Transfer temporary array to worksheet
TheRange.Value = TempArray

'Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - starttime, "00.00") & " seconds"
End Sub

Mavyak
08-29-2008, 05:20 PM
I think this is what you're looking for but I'm not exactly positive what you are trying to acheive:
Sub ArrayFillRange()
'Fill a range by transferring an array
Dim TempArray() As Integer
Dim TheRange As Range

'Get the dimensions
cellsdown = 1000
cellsacross = 5

'Record starting time
starttime = Timer

'Redimension temporary array
ReDim TempArray(1 To cellsdown, 1 To cellsacross)

'Set worksheet range
Set TheRange = ActiveCell.Range(Cells(1, 1), _
Cells(cellsdown, cellsacross))

'Fill the tempoary array
currval = Me.Range("B1").Value
Application.ScreenUpdating = False
For i = 1 To cellsdown
For j = 1 To cellsacross
TempArray(i, j) = currval + j
Next j
currval = currval + 1
Next i

'Transfer temporary array to worksheet
TheRange.Offset(6, 0).Value = TempArray

'Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - starttime, "00.00") & " seconds"
End Sub

Bob Phillips
08-29-2008, 05:22 PM
Just change



'Transfer temporary array to worksheet
TheRange.Value = TempArray


to



'Transfer temporary array to worksheet
TheRange = TempArray

TrippyTom
08-29-2008, 06:21 PM
Holy crap. Thanks XLD. :)

My main goal was to avoid pasting row-by-row, which is quite slow when having to do 1000 rows. I think I can incorporate your solution into my real worksheet now. Thanks so much!

-TT

TrippyTom
08-29-2008, 06:41 PM
ok, well that did exactly what my post asked. But I guess I need to clarify someting:

Those results are not the actual values I need; I was using those values as an example. Do I have to put the calculations in the loop itself? Or can i just copy whatever the results are (B4:F4) when I incremenet b1 and somehow put that in the array?

The results for each cell have very complicated formulas in the original file. I'm not sure how I would incorporate that in the code, if that's what I have to do.

TrippyTom
08-29-2008, 07:24 PM
so what I need it to do is:
For each iteration of changing B1 (1 - 1000), I need to copy whatever values are in cells (B4:F4), every time.

1) B1 = 1, copy B4:F4 into array
2) B1 = 2, copy B4:F4 into next row of array
3) B1 = 3, copy B4:F4 into next row of array
... (etc)

I hope that makes more sense?

Mavyak
08-29-2008, 09:06 PM
Do you want the data copied to the array automatically when one cell is changed for that respective row or do you want it as a macro you can run that will copy the range B1:F1000 into an array whenever you run it? Also, what will become of the populated array? Will it be displayed else where?

david000
08-29-2008, 11:24 PM
Range("b5").Resize(999, 5) = Range("b4").Resize(, 5).Value

TrippyTom
08-30-2008, 10:28 AM
Mavyak:
I want it as a macro to run that will:
1) start b1=1, copy b4:F4 to array
2) next iteration b1=2, copy b4:f4 to array
3) next iteration b1=3, copy b4:f4 to array
...
1000) finally b1=1000, copy b4:f4 to array

Then dump the entire array into cells b7:f1006.


David:
Your code just copies the cells at one time. But the values change every time I increment B1. That's why I need to copy the same range after each iteration. And I was hoping an array could do that faster than doing it manually.

Mavyak
08-30-2008, 11:01 AM
Sub Copy_B4F4_To_Array()
Dim x As Integer, i As Integer
Dim MyArray(0 To 999, 0 To 4)
For x = 0 To 999
Me.Range("B1").Value = x + 1
For i = 0 To 4
MyArray(x, i) = Me.Range("B4").Offset(0, i)
Next i
Next x
Me.Range("B7:F1006").Value = MyArray()
End Sub
That goes in the worksheet module.

(Edit: Tweaked to fit in one sub-procedure)

TrippyTom
08-30-2008, 12:23 PM
Thanks Mavyak,
that seems to be exactly what I needed. (I changed it to B1 and it worked)

I'll have to try it out when I get back to work on Tuesday. Thanks a lot. :)

Mavyak
08-30-2008, 12:32 PM
I don't know what I was thinking. I knew it should have been B1.

I did notice that the following line needs to be put in immediatedly following the B1 increment:

Me.Calculate

and before the loop that stores the values to the array like this:

...
Me.Range("B1").Value = x + 1
Me.Calculate
For i = 0 To 4
...

david000
08-30-2008, 08:59 PM
I'm not entirely sure what the goal is here, but according to your attachment it looks like your filling a series so I'll throw this out if you need a back-up plan.
Works exactly like the attachment...
Sub testClick()
Dim row As Integer
Dim col As Integer
Dim c As Integer


Range("b7").Resize(, 5) = Range("b4").Resize(, 5).Value
row = 7
c = 0
Do
For col = 0 To 5
Range("b7:b" & row).Offset(, col).DataSeries Step:=1
Next col
row = row + 1
c = c + 1
Range("b1") = c

Loop Until c = 100 'AUCHTUNG!!! CHANGE AT YOUR OWN RISK!

End Sub