CODEWITHSUNDEEP
excelvba
David
David

Reputation: 71

Summing Ranges In VBA

I am not very versed in coding so this may not be correct lingo, but I am trying to do a very simple thing, sum ranges. By that I mean I want:

Sub Test()
Range("E2:E9") = Range("D2:D9") + Range("G2:G9")
End Sub

This works fine if I only use:

Sub Test()
Range("E2") = Range("D2") + Range("G2")
End Sub

From this all I want to happen is get everything in cells E2-E9 to be equal to its relative addition of D_ and G_. So E4 will equal D4+G4 for example.

This is just a test for a larger project with 100+ rows which is why I don't want to do them all manually.

Maybe I need to use some sort of array addition or something? I am not sure.

Upvotes: 1

Views: 837

Answers (3)

hnk
hnk

Reputation: 2214

You can make a custom formula to do so.

For e.g. you want to sum A1:A10 and B1:B10 and need the results in C1:C10 (do note that you'll need the same number of rows here, and both vertical)

Sub AddRanges(Rng1 As Range, Rng2 As Range, ResultRng As Range)
    Dim i As Long, N As Long
    N = Rng1.Rows.Count

    For i = 1 To N
        ResultRng(i, 1).Value = Rng1(i, 1).Value + Rng2(i, 1).Value
    Next i
End Sub

This formula calls the above subroutine 

Sub TestMyFormula()
    Dim RngA As Range, RngB As Range, RngC As Range
    Set RngA = ActiveSheet.Range("A1:A10")
    Set RngB = ActiveSheet.Range("B1:B10")
    Set RngC = ActiveSheet.Range("C1:C10")

    Call AddRanges(RngA, RngB, RngC)

End Sub

Upvotes: 0

L42
L42

Reputation: 19737

Use the Range Object Formula property like this:

Edit1: Avoid circular ref as what Mark Balhoff pointed out

With Range("E2:E9")
    .Offset(0, 2).Value = .Offset(0, 2).Value '~~> avoid circular ref
    .Formula = "=D2+G2"
    .Value = .Value
    .Offset(0, -1).ClearContents
    .Offset(0, 2).Formula = "=E2" '~~> if you still want to have it reflected in G
End With

Or you can add D to E directly without using a helper column G.

With Range("E2:E9")
    .Offset(0, -1).Copy '~~> copy what's in D
    .PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '~~> add in E
    .Offset(0, -1).ClearContents '~~> clear whatever's in D
End With

Upvotes: 1

Chel
Chel

Reputation: 2623

Here's how you would do it as an array function:

Range("E2:E9").FormulaArray = "=D2:D9+G2:G9"

But array functions can be unwieldy, so you might just want to do it as single-cell formulas:

Range("E2:E9").Formula = "=D:D+G:G"

Excel does a little bit of magic to make that formula work. If you want to be explicit:

Range("E2:E9").FormulaR1C1 = "=RC[-1]+RC[2]"

Upvotes: 4

Related Questions