Macro to sum data

[Hiport]

Hi, how do i get a macro to do a sum at the end of each second column ?, so i need to sum the values in Col 2, 4,6 etc, but the macro should know when the last data in the column ends

I need the formula should skip a row after the data ends so its easy to see all the summed amounts, so for example say if the data ends in row B35, then the formula should be in B37, then D37, F37 etc

Can this be achieved?

 

[jindon]

Correction

Sub test()
Dim LastC As Integer, i As Integer
With ActiveSheet
    LastC = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    With .Range("b3").End(xlDown).Offset(2,-1).Resize(,LastC)
        For i = 2 To LastC Step 2
            .Cells(i).Formula ="=sum(r3c:r[-2]c)"
        Next
    End With
End With
End Sub

 

[Peter_SSs]

hi peter, your code worked but it did not sum the last column, how do i just the code to accomodate the last column

same goes for me peter, having the same problem as Katy

Was my second assumption incorrect?

Here's my sheet after running the code. What's different with yours? Give me something concrete in terms of columns used headings etc to work with.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Headings	Headings	Headings	Headings	Headings	Headings	Headings
2	Headings	Headings	Headings	Headings	Headings	Headings	Headings
3		3		5		9
4		3		5		9
5		3		5		9
6		3		5		9
7		3		5		9
8		3		5		9
9		3		5		9
10		3		5		9
11		3		5		9
12		3		5		9
13
14		30		50		90
15
Sum below vba

 

[Hiport]

peter everything in your spreadsheet is correct but the last column of the data will always have values, but the column itself is not always in the same column ref, so the last column may not always be say Col Z it may be say col Y, but whereever the last column is it will always have values.

This is how my spreadsheet looks like.

0106 2008 0206 2008 0306 2008 0406 2008
P'folio Amount P'folio Amount P'folio Amount P'folio Amount

3 3 3 3



Hope this info is ok to go on.

Was my second assumption incorrect?

Here's my sheet after running the code. What's different with yours? Give me something concrete in terms of columns used headings etc to work with.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Headings	Headings	Headings	Headings	Headings	Headings	Headings
2	Headings	Headings	Headings	Headings	Headings	Headings	Headings
3		3		5		9
4		3		5		9
5		3		5		9
6		3		5		9
7		3		5		9
8		3		5		9
9		3		5		9
10		3		5		9
11		3		5		9
12		3		5		9
13
14		30		50		90
15
Sum below vba

 

[Hiport]

my example above did not come out right, the 3 is suposed to be under each heading "Amount", every second column

peter everything in your spreadsheet is correct but the last column of the data will always have values, but the column itself is not always in the same column ref, so the last column may not always be say Col Z it may be say col Y, but whereever the last column is it will always have values.

This is how my spreadsheet looks like.

0106 2008 0206 2008 0306 2008 0406 2008
P'folio Amount P'folio Amount P'folio Amount P'folio Amount

3 3 3 3



Hope this info is ok to go on.

 

[Peter_SSs]

OK, what I think you are saying is that the columns to sum are not necessarily in the "even" columns B, D, F etc. So is this what we are trying to do? "Starting from the right-most column sum it and every second column (moving to the left) until we reach the left of the sheet"? If so, try this code instead.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Sum_Under_Even()<br>    <SPAN style="color:#00007F">Dim</SPAN> lastcolumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> firstcolumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column<br>    firstcolumn = 1 + lastcolumn Mod 2<br>    <SPAN style="color:#00007F">With</SPAN> Cells(Rows.Count, firstcolumn + 1).End(xlUp).Offset(2, -1)<br>        .Offset(, 1).Formula = "=Sum(R3C:R[-2]C)"<br>        .Resize(, 2).Copy Destination:=.Resize(, lastcolumn - firstcolumn + 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Hiport]

Peter your code stalls at the below line, not to worry mate, I have used Jindons code

.Resize(, 2).Copy Destination:=.Resize(, lastcolumn - firstcolumn + 1)

OK, what I think you are saying is that the columns to sum are not necessarily in the "even" columns B, D, F etc. So is this what we are trying to do? "Starting from the right-most column sum it and every second column (moving to the left) until we reach the left of the sheet"? If so, try this code instead.


Sub Sum_Under_Even()
Dim lastcolumn As Long
Dim firstcolumn As Long

lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
firstcolumn = 1 + lastcolumn Mod 2
With Cells(Rows.Count, firstcolumn + 1).End(xlUp).Offset(2, -1)
.Offset(, 1).Formula = "=Sum(R3C:R[-2]C)"
.Resize(, 2).Copy Destination:=.Resize(, lastcolumn - firstcolumn + 1)
End With
End Sub