CODEWITHSUNDEEP
vbaexcel
user8330379
user8330379

Reputation:

Summing up two arrays or matrices element by element

I have two variables defined like this:

Per_Mnd = Worksheets("Sheet1").Range("G2:G8").Value
Per_Mnd2 = Worksheets("Sheet2").Range("G2:G8").Value

Obviously both Per_Mnd and Per_Mnd2 have 7 rows and 1 column. Now I want to sum them up element by element, getting another 7×1 array. How do I do it?

And what is they are defined by matrix

Per_Mnd = Worksheets("Sheet1").Range("G2:H8").Value
Per_Mnd2 = Worksheets("Sheet2").Range("G2:H8").Value

How can I quickly sum them up element by element?

thanks!

Upvotes: 2

Views: 1077

Answers (2)

Scott Craner
Scott Craner

Reputation: 152525

You can simply evaluate an INDEX formula to return the array:

Sub Test()
Dim oarr As Variant
Dim Per_Mnd As Variant
Dim Per_Mnd2 As Variant
Per_Mnd = Worksheets("Sheet1").Range("G2:G8").Value
Per_Mnd2 = Worksheets("Sheet2").Range("G2:G8").Value

With Application
    oarr = .Transpose(.Evaluate("INDEX({" & Join(.Transpose(Per_Mnd), ",") & "}+{" & Join(.Transpose(Per_Mnd2), ",") & "},)"))
End With


Debug.Print oarr(3, 1)
End Sub

Note: this only works with single column arrays of the same size.

Upvotes: 1

Vityata
Vityata

Reputation: 43585

If you want to sum a matrix, the VBA way is WorksheetFunction.SumProduct

Take the example below, returning 60 -> 1*10+2*10+3*10

enter image description here

Public Sub TestMe()

    Dim el1 As Variant
    Dim el2 As Variant
    Dim res As Variant

    el1 = Application.Transpose(Range("A1:A3"))
    el2 = Application.Transpose(Range("B1:B3"))
    Debug.Print WorksheetFunction.SumProduct(el1, el2)

    ReDim res(1 To UBound(el1))
    Dim cnt As Long

    For cnt = LBound(el1) To UBound(el1)
        res(cnt) = el1(cnt) + el2(cnt)
    Next cnt

End Sub

The idea of Application.Transpose() is to present the Range() as a one dimensional array. Once we do so, we introduce res(1 to UBound(el1) where we write the product per element. Or you can even do the SumArray as a function, returning the new array:

Public Function SumArray(arr1 As Variant, arr2 As Variant) As Variant

    ReDim res(1 To UBound(arr1))
    Dim cnt As Long

    For cnt = LBound(arr1) To UBound(arr1)
        res(cnt) = arr1(cnt) + arr2(cnt)
    Next cnt

    SumArray = res

End Function

Upvotes: 0

Related Questions