Sum using Macro?

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

So sorry for my continuing inability to get my head around macros! I'm trying to workout a new spreadsheet and would really appreciate any time anyone can spare to help me.

Can I get a macro to add a total line to a variable number of rows? I want to specify the starting row each time, but then sum down to the row above the totals.
I've tried
ActiveCell.Offset(0, 3).Range("a1").Select
Dim x As Integer
x = InputBox("Which row to sum from?")
ActiveCell.FormulaR1C1 = "=SUM(R[x]C:R[-1]C)"
Range("a1").Select
With no luck.

If anyone can improve on this / scrap this and completely sart again, I'd be really grateful for your help.

Many thanks in advance,
Hipty

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
the sample data is like this


a 1
s 2
d 3
f 4
g 5
h 6
j 7
k 8
total 36

the total in col., B is total of all the data above this row

now try this macro


Sub test()
Dim x As Integer
x = InputBox("Which row to sum from?")
Cells(x, 2).End(xlDown) = WorksheetFunction.Sum(Range(Cells(x, 2), Cells(x, 2).End(xlDown).Offset(-1, 0)))
End Sub


FIRST copy the sample data on a sheet and copy the macrpo in vb editor and run the macro and check
if it is ok study the macro and your sheet and modify the macro.

if inputbox is 3 then the total will be form B3 to B8.