How to add arrays?

Question

I have the following problem in Excel while calculating through a loop: I need a variable (Destination Variable) that sequentially stores the results produced after each loop has been completed (avoiding the use of circular references) that would look like this:

'Let's call it "origin" variable in the worksheet
Origin Variable (50 x 50 array)
      1 2 4
      2 3 4
      2 2 3
'Let's call it "destination" variable in the worksheet
Destination Variable (50 x 50 array)
      1 1 1
      1 1 1
      1 1 1

After each loop, I'd need the macro to perform the following code:

range("destination").value = range("destination").value + range("origin").value

So that the destination variable would look like this after the current loop:

Destination Variable
      2 3 5
      3 4 5
      3 3 4

However, Excel does not allow me to perform the previous function.
Does anyone have an answer how this could be solved?

What is the error that you're getting? Can you give a larger sample of your code so we can see what's going on? — Telestia, May 08 '14 at 16:12
The error is as follows, Run-time error '1004': Method 'Range' of object '_Global' failed. Regarding the code, it is just this line (I have created a new excel with a macro that only does this operation and it still produces the same error) — user3617237, May 08 '14 at 16:14
I believe that error seems to arise from a named range that doesn't exist. — David Zemens, May 08 '14 at 16:23

score 6 · Accepted Answer · edited Jan 09 '15 at 01:53

6

Quite easy. I did this by recording as macro and tidying.

Sub Macro1()            
    Range("origin").Copy
    Range("destination").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

edited Jan 09 '15 at 01:53

L42

19,427
11
44
68

answered May 08 '14 at 16:15

S Meaden

8,050
3
34
65

Good option, hadn't though about it. I'll try and let you know if it works :) – user3617237 May 08 '14 at 16:17

score 3 · Answer 2 · answered May 08 '14 at 16:22

I like @S Meaden's answer, it is simple and I had not thought of that. And it certainly works for this purpose.

You could also do simple iteration. IN the example below I add two different ranges and put them in a third range, but this could be re-worked for your needs pretty easily, or it is another option if you ever need to add ranges to another range:

Sub AddArrays()
Dim rng1 As Range
Dim rng2 As Range
Dim rngSum As Range

Dim arr1 As Variant
Dim arr2 As Variant
Dim arrSum As Variant

Set rng1 = Range("A1:C7")    '## Modify as needed
Set rng2 = Range("F1:H7")    '## Modify as needed
Set rngSum = Range("K1:M7")  '## Modify as needed

'Raises an error, Type Mismatch
'rngSum.Value = rng1.Value + rng2.Value

arr1 = rng1.Value
arr2 = rng2.Value
arrSum = rngSum.Value

Dim x As Integer, y As Integer

    For x = LBound(arr1, 1) To UBound(arr1, 1)
        For y = LBound(arr1, 2) To UBound(arr1, 2)
            arrSum(x, y) = arr1(x, y) + arr2(x, y)
        Next
    Next

    'Print result to sheet
    rngSum.Value = arrSum
End Sub

This is better using the sheet is not scale-able. – OfficialBenWhite Nov 05 '15 at 16:06 — OfficialBenWhite, Nov 05 '15 at 16:06

How to add arrays?

2 Answers2

Linked