Getting a value from a different column but same row and adding it to a sum using VBA

Question

I'm having trouble with this code. The code goes through one column (column B) and whenever it sees a specific word it would take the value located in column D. e.g if the key word is located in B2 then it would take the value from D2. Below I have coded what I have so far.

Dim MyRange As Range
Dim rcell As Range
Dim i As Integer
Dim Sum_Payment As Double

 Set MyRange = Worksheets("Sheet1").Range("B2:B50")

    For Each rcell In MyRange.Cells
            Debug.Print rcell.Address, rcell.Value
            For i = 2 to 50
            If rcell.Value = "Payment" Then
            Sum_Payment = Sum_Payment + Cells(i, 4).Value
            Debug.Print Sum_Payment
            ThisWorkbook.Sheets("Sheet2").Activate
            ThisWorkbook.Sheets("Sheet2").Range("B2") = Sum_Payment
    End If
    Next i
Next rcell

Also, after you `Activate` Sheet2, you never go back to the original sheet, which may cause issues. It's best to avoid using [`.Select/.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — BruceWayne, Sep 23 '16 at 18:58

score 0 · Accepted Answer · answered Sep 23 '16 at 18:59

As per my comment this can be done with the following formula:

=SUMIF(Sheet1!B:B,"Payment",Sheet1!D:D)

But if you want the vba:

The second loop is not needed.
The output on Sheet2 should be after the loop.
No need to activate the sheet.

Code:

Dim MyRange As Range
Dim rcell As Range
Dim i As Integer
Dim Sum_Payment As Double

Set MyRange = Worksheets("Sheet1").Range("B2:B50")

For Each rcell In MyRange.Cells
    Debug.Print rcell.Address, rcell.Value

    If rcell.Value = "Payment" Then
        Sum_Payment = Sum_Payment + rcell.Offset(0,2).Value
        Debug.Print Sum_Payment
    End If
Next rcell
ThisWorkbook.Sheets("Sheet2").Range("B2") = Sum_Payment

Getting a value from a different column but same row and adding it to a sum using VBA

1 Answers1