CODEWITHSUNDEEP
excelvbamatrixmultidimensional-arraysum
ante031
ante031

Reputation: 3

VBA Sum of two 2D arrays on different sheets

I am trying to add/subtract matrix on SheetA to/from matrix on SheetB and print result on SheetA+B. This code works, but when I use matrix that is not square, then I get error subscript out of range. Any ideas what to do? Matrix A and B are same size, but size is user defined and always starts in A1.

Private Sub CommandButton5_Click()
'''''''''''''''''''A+B'''''''''''''''''''''''''

'determining matrix size

Worksheets("A").Activate

Dim a As Integer
Dim b As Integer

lastcol = ActiveSheet.Range("a1").End(xlToRight).Column
lastrow = ActiveSheet.Cells(65536, lastcol).End(xlUp).Row
a = lastcol
b = lastrow

Dim matricaA As Range
Dim matricaB As Range

With Sheets("A")
    lastcol = ActiveSheet.Range("a1").End(xlToRight).Column
    lastrow = ActiveSheet.Cells(65536, lastcol).End(xlUp).Row
    Set matricaA = ActiveSheet.Range("a1", ActiveSheet.Cells(lastrow, lastcol))
End With

With Sheets("B")
    'lastcol = ActiveSheet.Range("a1").End(xlToRight).Column
    'lastrow = ActiveSheet.Cells(65536, lastcol).End(xlUp).Row
    Set matricaB = ActiveSheet.Range("A1", ActiveSheet.Cells(lastrow, lastcol))
End With

Dim rngA As Range
Dim rngB As Range
Dim rngSum As Range

Dim arrA As Variant
Dim arrB As Variant
Dim arrSum As Variant


Worksheets("A").Activate
With Sheets("A")
    lastcol = ActiveSheet.Range("a1").End(xlToRight).Column
    lastrow = ActiveSheet.Cells(65536, lastcol).End(xlUp).Row
    Set rngA = ActiveSheet.Range("a1", ActiveSheet.Cells(lastrow, lastcol))
End With

Worksheets("B").Activate
With Sheets("B")
    lastcol = ActiveSheet.Range("a1").End(xlToRight).Column
    lastrow = ActiveSheet.Cells(65536, lastcol).End(xlUp).Row
    Set rngB = ActiveSheet.Range("A1", ActiveSheet.Cells(lastrow, lastcol))
End With

    Application.Goto ActiveWorkbook.Sheets("A+B").Range("A1").Resize(b, a)
Worksheets("A+B").Activate
With Sheets("A+B")
    lastcol = b
    lastrow = a
    Set rngSum = ActiveSheet.Range("A1", ActiveSheet.Cells(lastrow, lastcol))
End With

arrA = rngA.Value
arrB = rngB.Value
arrSum = rngSum.Value

Dim x As Integer, y As Integer

For x = LBound(arrA, 1) To UBound(arrA, 1)
    For y = LBound(arrA, 2) To UBound(arrA, 2)
       arrSum(x, y) = arrA(x, y) + arrB(x, y)    '...error - subscript out of range
    Next                                         ' when matrix is non square
Next

'Print result to sheet
rngSum.Value = arrSum



End Sub

Upvotes: 0

Views: 720

Answers (1)

Tim Williams
Tim Williams

Reputation: 166366

Dim rng as Range

Set rng = worksheets("A").Range("A1").currentregion
rng.copy worksheets("A+B").range("A1")

worksheets("A").Range(rng.address()).copy
worksheets("A+B").range("A1").pastespecial  Paste:=xlPasteValues, Operation:=xlAdd

Upvotes: 1

Related Questions