Active Cell equals the value of two other cells

trivera648

New Member
Joined
Jan 29, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
  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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 
Solution

trivera648

New Member
Joined
Jan 29, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,575
Messages
5,765,188
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top