Active Cell equals the value of two other cells

[trivera648]

Hello everyone,

I would like to sum up the value from two cells located in the same row, onto a third cell also located in the same row. I'm having trouble coming up with the easiest way to do this.

The amount of rows in the worksheet always changes.

As you can see below, in my "For Each Cell In ARng" I've tried doing offset, =Sum, I can't seem to get it right.

The two values to be added together are always going to be on the same row, columns J and L. And the sum should always come out in Column N.

Any help would be most appreciated.

  VBA Code:     
   
       Code:

Sub Test3()

'TOTAL AMOUNT COLUMN SUMMED AUTO
                Dim ARng As Range
                Dim ALastCell As Range
                Dim ACell As Range
            
            
            'DEFINING LAST CELL IN COLUMN N (TOTAL REFUND AMOUNT)
               Set ALastCell = Cells(Rows.Count, "N").End(xlUp)
                
            'DEFINING "RNG" AS RANGE FROM N2 TO LAST CELL
               Set ARng = Range("N2", ALastCell)
                
                
           For Each Cell In ARng
                'Cell.Value = Cells( + ActiveCell.Offset(-2)
'                'Cell.Value = "=SUM(OFFSET(-4,) + (-2,)"
                 '.FormulaR1C1 = "=SUM(RC[-4,]:RC[-2,])"
                    'ActiveCell.Formula = "=Sum(" & ActiveCell.Offset(-4) & "+" & ActiveCell.Offset(-2)

   
                    Next Cell



End Sub

 

[Joe4]

A few things:
1. You do not need a loop. You can apply the formula to the whole range at once.
2. If you are putting formulas in column N, you probably don't want to use column N to locate the last row. Pick a column that already has data in it.

Try this:

Sub Test3()

'TOTAL AMOUNT COLUMN SUMMED AUTO
                Dim ARng As Range
                Dim ALastRow As Long
                Dim ACell As Range
           
           
            'FINDING LAST ROW IN COLUMN J WITH DATA
               ALastRow = Cells(Rows.Count, "J").End(xlUp).Row
               
            'DEFINING "RNG" AS RANGE FROM N2 TO LAST CELL
               Set ARng = Range("N2:N" & ALastRow)
           
            'ENTER FORMULA FOR ALL ROWS
               ARng.FormulaR1C1 = "=RC[-4]+RC[-2]"

End Sub

 

[trivera648]

A few things:
1. You do not need a loop. You can apply the formula to the whole range at once.
2. If you are putting formulas in column N, you probably don't want to use column N to locate the last row. Pick a column that already has data in it.

Try this:

Sub Test3()

'TOTAL AMOUNT COLUMN SUMMED AUTO
                Dim ARng As Range
                Dim ALastRow As Long
                Dim ACell As Range
          
          
            'FINDING LAST ROW IN COLUMN J WITH DATA
               ALastRow = Cells(Rows.Count, "J").End(xlUp).Row
              
            'DEFINING "RNG" AS RANGE FROM N2 TO LAST CELL
               Set ARng = Range("N2:N" & ALastRow)
          
            'ENTER FORMULA FOR ALL ROWS
               ARng.FormulaR1C1 = "=RC[-4]+RC[-2]"

End Sub

Joe4, You are a prince. Thank you.

 

[Joe4]

You are welcome.
Glad I was able to help.

Please don't hesitate to let me know if you have any questions about the code.