How to sum various rows in vba

Haikal

New Member
Joined
Jan 16, 2014
Messages
28
I am trying to the write the formula to sum up a row. My last column always varies but the first column is fixed. .
The formula i have is:
Code:
sFormula = "=SUM(RC[-6]:RC[-1])"

but is doesn't work for all exercises as some times the total column is "D" or "H"..

what i need is a function like this:
Code:
sFormula = "=SUM(RC[Number or columns - 1]:RC[-1])"

Example:
Column: A B C D E TOTAL
NRow 1: 00:00 3 4 5 6 18
NRow 2: 00:05 4 5 9
NRow 3: 00:10 7 9 11 27
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You can use an absolute R1C1 reference instead of a relative reference.

If your "fixed" column is Column B, then use ...
Code:
sFormula = "=SUM(RC2:RC[-1])"
 
Upvote 0
the Sum column varies..

i want a tool to select the last column, and then apply this formula to the last row.
 
Upvote 0
the Sum column varies..

i want a tool to select the last column, and then apply this formula to the last row.

If the formula is placed in the cell to the right of the last value in the row, it will sum the values on the same row from column B to the last column.

Here's an example that will enter that formula on the ActiveCell's row....

Code:
Sub AddSumToThisRow()
 Dim lLastCol As Long

 With ActiveCell
   lLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column
   If lLastCol > 1 Then _
      Cells(.Row, lLastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"
 End With
End Sub
 
Upvote 0
it works but only for one row, what about making it for all rows? (i.e till the last row)
 
Upvote 0
it works but only for one row, what about making it for all rows? (i.e till the last row)

Code:
Sub AddSumToEachRow()
 Dim lLastCol As Long, lLastRow As Long
 Dim lCurrentRow As Long
   
 Const lFirstRow = 2
 
 With ActiveSheet
   lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

   For lCurrentRow = lFirstRow To lLastRow
      lLastCol = Cells(lCurrentRow, Columns.Count).End(xlToLeft).Column
      If lLastCol > 1 Then _
         Cells(lCurrentRow, lLastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"
   Next lCurrentRow
 End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top