macro total for colums with changing # of rows

[Guest]

I'd like to build a macro that sums up four colums in a worksheet. The
workbook is generated automatically by a database program. Once I open the
workbook i have a macro that does all the formatting for me, but i have to go
in and manually totall the last four collums of the sheet.

These columns don't move they are the same in every sheet.
The number of rows varies from one report to the next.
The cells are all continous- in a rectangle shape-no empty cell inside the
report area.

i need some code that can sum the columns even thought the number or rows is
not constant each time i run the macro.

Any help is greatly apprecieated.

Billy

 

[Guest]

Try this... It Adds the column total at the bottom of the columns you specify
(A and B in this case).

Sub Test()
ColumnTotal "A"
ColumnTotal "B"
End Sub

Sub ColumnTotal(ByVal strColumn As String)
Cells(Rows.Count, strColumn).End(xlUp).Offset(1, 0).Value = _
Application.Sum(Columns(strColumn))
End Sub

 

[Guest]

Jim, thanks that works great. I added it to my macro with a few
adjustments(increasing offset to 2 so there is a space between the data and
totals).

I have another question. I have this running on two separate sheets in the
same workbook. I've made adjustments so that it works. I need to copy the
results from the first sheet and past them right below the totals for the
second sheet to compare them.(they are supposed to be equal). is there a way
to do this.

heres what i have so far

Sheets("1").Select
ColumnTotal "f"
ColumnTotal "g"
ColumnTotal "h"
ColumnTotal "i"
Sheets("2").Select
ColumnTotal "H"
ColumnTotal "I"
ColumnTotal "J"
ColumnTotal "K"

End Sub

Sub ColumnTotal(ByVal strColumn As String)
Cells(Rows.Count, strColumn).End(xlUp).Offset(2, 0).Value = _
Application.Sum(Columns(strColumn))
End Sub

 

[Guest]

Hi,
Building on Jim's code:

HTH

Sub x()

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

ws1.Select
ColumnTotal "f"
ColumnTotal "g"
ColumnTotal "h"
ColumnTotal "i"
ws2.Select
ColumnTotal "H"
ColumnTotal "I"
ColumnTotal "J"
ColumnTotal "K"
CompareTotal "f", ws1, "h", ws2
CompareTotal "g", ws1, "i", ws2
CompareTotal "h", ws1, "j", ws2
CompareTotal "i", ws1, "k", ws2
End Sub

Sub ColumnTotal(ByVal strColumn As String)
Cells(Rows.Count, strColumn).End(xlUp).Offset(2, 0).Value = _
Application.Sum(Columns(strColumn))
End Sub

Sub CompareTotal(ByVal strColumn1 As String, ByVal ws1 As Worksheet, ByVal
strColumn2 As String, ByVal ws2 As Worksheet)
ws2.Cells(Rows.Count, strColumn2).End(xlUp).Offset(2, 0).Value = _
ws1.Cells(Rows.Count, strColumn1).End(xlUp).Value
End Sub

 

[Guest]

I removed the selects to clean things up a bit. I was not sure what to do if
the columns did not match so I just popped a message box...

Sub test()
ColumnTotal "f", Sheets("1")
ColumnTotal "g", Sheets("1")
ColumnTotal "h", Sheets("1")
ColumnTotal "i", Sheets("1")
ColumnTotal "H", Sheets("2")
ColumnTotal "I", Sheets("2")
ColumnTotal "J", Sheets("2")
ColumnTotal "K", Sheets("2")
If CompareColumns("f", Sheets("1"), "H", Sheets("2")) = False Then _
MsgBox "Error1"
If CompareColumns("g", Sheets("1"), "I", Sheets("2")) = False Then _
MsgBox "Error2"
If CompareColumns("h", Sheets("1"), "J", Sheets("2")) = False Then _
MsgBox "Error3"
If CompareColumns("i", Sheets("1"), "K", Sheets("2")) = False Then _
MsgBox "Error4"

End Sub

Sub ColumnTotal(ByVal strColumn As String, ByVal wks As Worksheet)
wks.Cells(wks.Rows.Count, strColumn).End(xlUp).Offset(2, 0).Value = _
cApplication.Sum(wks.Columns(strColumn))
End Sub

Function CompareColumns(ByVal strColumn1 As String, ByVal wks1 As Worksheet, _
ByVal strColumn2 As String, ByVal wks2 As Worksheet) As
Boolean
If wks1.Cells(Rows.Count, strColumn1).End(xlUp).Value = _
wks2.Cells(Rows.Count, strColumn2).End(xlUp).Value Then
CompareColumns = True
Else
CompareColumns = False
End If
End Function

 

[Guest]

I tried these last two and couldnt bet either to work. compare totals and
comparecolumns were both in red in the vbe.