Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding arrays
I have 2 arrays that I have created in a macro. These arrays contain integer values and are 19x3 in size. The code to create the arrays is:
Workbooks(ResultsWB).Sheets("Daily Results - # Orders").Activate Range("B5").Select For j = 0 To 3 For i = 0 To 19 DailyArray(i, j) = ActiveCell.Offset(i, j).Value Next i Next j Workbooks(CumWB).Sheets("Cum Order Data").Activate Range("B5").Select For i = 0 To 3 For j = 0 To 19 PrevCumArray(i, j) = ActiveCell.Offset(i, j).Value Next j The arrays are created correctly. What I want to do, and can't seem to figure out the code to do it, is add the arrays together to create a third array (add the corresponding elements of each array). I am trying to do this in code using the array as a variable. If I want to create the third array as the values in a block of cells on a work sheet, using the "FormulaArray = " works, With Selection .FormulaArray = "=RC[-5]:R[19]C[-2]+'Cum Order Data'!R[-2]C[-5]:R[17]C[-2]" but I then have to copy the results and paste them into the worksheet in a new place. I cannot create the array on the worksheet in its final location because I will be over-writing one of the arrays used in the formula. Thanks in advance for the help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding arrays
I'm not sure I'm following; aren't the arrays, which go from 0 to 19 and
0 to 3, 20x4 in size? In any event, it looks like you're seeking: Dim arr3(0 To 19, 0 To 3) For i = 0 To 19: For j = 0 To 3 arr3(i, j) = DailyArray(i, j) + PrevCumArray(i, j) Next: Next For what it's worth, if the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you could use: arr3 = ArrayAdd(DailyArray,PrevCumArray) Alan Beban Dan wrote: I have 2 arrays that I have created in a macro. These arrays contain integer values and are 19x3 in size. The code to create the arrays is: Workbooks(ResultsWB).Sheets("Daily Results - # Orders").Activate Range("B5").Select For j = 0 To 3 For i = 0 To 19 DailyArray(i, j) = ActiveCell.Offset(i, j).Value Next i Next j Workbooks(CumWB).Sheets("Cum Order Data").Activate Range("B5").Select For i = 0 To 3 For j = 0 To 19 PrevCumArray(i, j) = ActiveCell.Offset(i, j).Value Next j The arrays are created correctly. What I want to do, and can't seem to figure out the code to do it, is add the arrays together to create a third array (add the corresponding elements of each array). I am trying to do this in code using the array as a variable. If I want to create the third array as the values in a block of cells on a work sheet, using the "FormulaArray = " works, With Selection .FormulaArray = "=RC[-5]:R[19]C[-2]+'Cum Order Data'!R[-2]C[-5]:R[17]C[-2]" but I then have to copy the results and paste them into the worksheet in a new place. I cannot create the array on the worksheet in its final location because I will be over-writing one of the arrays used in the formula. Thanks in advance for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA arrays | Excel Discussion (Misc queries) | |||
Use of IF with arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
vba adding arrays | Excel Discussion (Misc queries) | |||
Adding Arrays | Excel Programming |