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?

 

[Peter_SSs]

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?

1. Can you confirm that all these "even" columns (B, D, F, etc) have the same number of rows of data. That is if column B data goes to row 35 then so do all the others.

2. What row does the numerical data start in?

3. How far across the sheet does the data go? That is, what is the last column requiring a sum? If this is not fixed, then is the last column requiring a sum the last column containing data in, say, row 1 or row 2?

 

[Hiport]

Hi Peter, i can confirm that all the even columns have the same number of rows of data, the numerical data starts at row 3, the last column is not fixed it depends which month you are in, some months have 31 days and some 30 and also Feb is 28 (apart from leap year).

Do you require more information?

1. Can you confirm that all these "even" columns (B, D, F, etc) have the same number of rows of data. That is if column B data goes to row 35 then so do all the others.

2. What row does the numerical data start in?

3. How far across the sheet does the data go? That is, what is the last column requiring a sum? If this is not fixed, then is the last column requiring a sum the last column containing data in, say, row 1 or row 2?

 

[MrKowz]

I sometimes like to have the macro determine how many of the values are not empty and store this as a variable on the spreadsheet.

An example could be:

Range("A1").Select
ActiveCell.Formula = "=COUNTA(A2:A20000)"
x = Range("A1").Value + 2
Range("A" & x).Select

And here is where I get stuck, because I still havent figured out how to do =SUM(A2:A & x) to work

 

[Hiport]

just wondering if anyone has had a chance to look at my problem above?

 

[jindon]

try

Sub test()
Dim LastC As Integer, i As Integer
With ActiveSheet
    LastC = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    With .Range("b3").End(xlDown).Offset(,-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

 

[stanleydgromjr]

Hiport,

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
30		28		28		28		28		28		28		28		28
31		29				29		29		29		29		29		29
32		30				30		30		30		30				30
33		31				31				31		31
34
35		496		406		496		465		496		496		435		465
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit
Sub CreateSumFormulae()
    Dim lngLastCol As Long
    Dim lngLoopCtr As Long
    lngLastCol = Sheets("Sheet1").Cells(1, 1).Offset((LastCell(ActiveSheet).Row - 1), LastCell(ActiveSheet).Column - 1).Range("A1").Column
    For lngLoopCtr = 2 To lngLastCol Step 2
        Cells(35, lngLoopCtr).FormulaR1C1 = "=SUM(R[-32]C:R[-2]C)"
    Next lngLoopCtr
End Sub

Function LastCell(ws As Worksheet) As Range
' Identifying the Real Last Cell
' Rodney Powell, Microsoft MVP - Excel, http://www.beyondtechnology.com/geeks012.shtml
'
    Dim lngLastRow, lngLastCol As Long
' Error-handling is here in case there is not any
' data in the worksheet
    On Error Resume Next
    With ws
        ' Find the last real row
        lngLastRow = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByRows).Row
        ' Find the last real column
        lngLastCol = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByColumns).Column
    End With
    ' Finally, initialize a Range object variable for
    ' the last populated row.
    Set LastCell = ws.Cells(lngLastRow, lngLastCol)
End Function

Then run the "CreateSumFormulae" macro.


Have a great day,
Stan

 

[Peter_SSs]

Assuming...
- nothing in column A in the row the totals will go in, and
- row 1 can be used to determine the right hand edge of the data
... this is my suggestion.

<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>    <br>    lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column<br>    lastcolumn = lastcolumn - lastcolumn Mod 2<br>    <SPAN style="color:#00007F">With</SPAN> Range("B" & Rows.Count).End(xlUp).Offset(2, -1)<br>        .Offset(, 1).Formula = "=Sum(R3C:R[-2]C)"<br>        .Resize(, 2).Copy Destination:=.Resize(, lastcolumn)<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>

 

[Katy Jordan]

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

Assuming...
- nothing in column A in the row the totals will go in, and
- row 1 can be used to determine the right hand edge of the data
... this is my suggestion.


Sub Sum_Under_Even()
Dim lastcolumn As Long

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

 

[Hiport]

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

Assuming...
- nothing in column A in the row the totals will go in, and
- row 1 can be used to determine the right hand edge of the data
... this is my suggestion.


Sub Sum_Under_Even()
Dim lastcolumn As Long

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