What have i done wrong with this macro, code wont work?

[tonywatsonhelp]

Hi everyone,

I've tried to write a simple macro then put it in a loop, but i've never done my own loops before, I google who and followed the directions as much as posible but have not had any luck,
I think i might be close but as always I'd be here all week trying to figure out what is wrong as i haven't a CLUE?

Please can someone tell me whats done wrong or whats missing?

It would be really hand to have this as i could adapt the loop into many other jobs as well?

Here the code

Sub SubTotalAll()
Dim xrow As Long
xrow = 2
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(x1Up).Row


Do Until xrow = lastrow + 1
   
    
    
    Range("M1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.FormulaR1C1 = "=SUM(R[-24]C:R[-1]C)"
    
    
    
    xrow = xrow + 1
    Loop
End Sub

Please help i think i'm close,

to summaries, the code is trying to Select M1, find blank cell input formula and do it again

Thanks

Tony

 

[kevatarvind]

Please Provide Some Sample Data and Details what actually you want to do ?

 

[sheetspread]

Use this one instead:

Excel 2010
	A	L	M
1	A		M
2	JOHN		200
3	MARY		100
4	TOM		300
5
6	JOE		600
7	BILL		400
8	GEFF		1000
9	HARRY		600
10	JO		800
11
12	BOB		200
13	BILL		500
14	SUE		600
15
16	YORIK		100
17	HAMLET		200
18	LEAR		300
19	BETH		700
20
21	TOM		200
22	BILL		500
Sheet9

Sub SumGroupsV3()
  Dim Ar As Range
  On Error GoTo NoData
  For Each Ar In Columns("M").SpecialCells(xlConstants).Areas
    Ar(1).Offset(Ar.Count).Formula = "=SUM(" & Ar.Address & ")"
  Next
NoData:
End Sub

Excel 2010
	A	L	M
1	A		M
2	JOHN		200
3	MARY		100
4	TOM		300
5			600
6	JOE		600
7	BILL		400
8	GEFF		1000
9	HARRY		600
10	JO		800
11			3400
12	BOB		200
13	BILL		500
14	SUE		600
15			1300
16	YORIK		100
17	HAMLET		200
18	LEAR		300
19	BETH		700
20			1300
21	TOM		200
22	BILL		500
Sheet9

Cell Formulas
Range	Formula
M5	=SUM($M$1:$M$4)
M11	=SUM($M$6:$M$10)
M15	=SUM($M$12:$M$14)
M20	=SUM($M$16:$M$19)

 

[tonywatsonhelp]

Thank you sheetspread, that has solved my problem,
I was hoping to also find out where i went wrong on the loop code?
I realise it can be difficult to to that sometimes but it would be really hand if i could understand what i did wrong?
I'm not worried about the code in the middle (yours is much better) but just w got an error and the code didn't run?

if anyone could just guide me on this it would be something i could use a lot?

thanks

Tony

 

[sheetspread]

It's Rick Rothstein's code

As for what you've written, I'm not sure you can automatically count the number of cells between spaces the same way the autosum button on the worksheet does. That's the problem with trying to extend code written by the macro recorder (note also the unneeded .select, activecell, and selection)

 

[tonywatsonhelp]

Hi sheetspread,
ok thanks very much for that,
I have actually managed to get my code working, but its nowhere near as good as what you gave me so big thanks.

Tony

 

[Zack Barresse]

It looks like this is input data. If so, I would highly recommend not adding formulas to it. Instead you can do a few things. If it's a Table then you should be using a PivotTable. If it's not a Table then you could think about using the Subtotals functionality (found on the Data tab).