Solved: Summing range and copying accross

[satyen]

Hello.

I would like to sum a range that may not always be the same size each time. Then copy this across until last heading just above it.

I have written some code to start off. but the comments explain what I would like it to do exactly...

[vba]Sub Testing()
Dim LCol
Dim LRow

LRow = Cells(Rows.Count, "b").End(xlUp).Row 'last populated ROW in B until col. A says Total Cars
LCol = Cells(Columns.Count, 15, 1).End(xlLeft).Columns 'this calculates last populated column above so the "15,1" needs to change"


ActiveCell.Resize(LRwsta - 1).FormulaR1C1 = "=sum(" & B2 & ":" & B & ")"
' Paste across until last populated cell above i.e. LCol.


End Sub[/vba]

[mdmackillop]

[VBA]Option Explicit
Sub Totals()
Dim c As Range
Dim LastCol As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set c = Columns(1).Find("Total Cars")
c.Offset(, 1).Resize(, LastCol - 1).Formula = "=SUM(B2:B" & c.Row - 1 & ")"
End Sub[/VBA]

Cells(Columns.Count, 15)

Remember to put Columns.Count as the second argument

[satyen]

I don't want the result to be in the same line as Total Cars so i changed the last line to be ActiveCell.Formula- but lost the across copy bit.

[vba]Sub Totals()
Dim c As Range
Dim LastCol As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set c = Columns(1).Find("Total Cars")
'c.Offset(, 1).Resize(, LastCol - 1).Formula = "=SUM(B2:B" & c.Row - 1 & ")"
ActiveCell.Formula = "=SUM(B2:B" & c.Row - 1 & ")"
End Sub[/vba]

yes good point about the Columns.count argument! - thanks.

[mdmackillop]

[VBA]Sub Totals()
Dim LastCol As Long
Dim LastRow As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

'Either
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
ActiveCell.Resize(, LastCol - 1).Formula = "=SUM(B2:B" & LastRow & ")"
'or
'ActiveCell.Resize(, LastCol - 1).Formula = "=SUM(B2:B" & ActiveCell.Row - 1 & ")"


End Sub
[/VBA]

[satyen]

I still needed it to add up to when col Says Total cars so i added this back in and amened the last line to include this.

[VBA]Sub Totals2()
Dim LastCol As Long
Dim LastRow As Long
Dim c As Range
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set c = Columns(1).Find("Total Cars")
'Either
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'ActiveCell.Resize(, LastCol - 1).Formula = "=SUM(B2:B" & LastRow & ")"
'or
ActiveCell.Resize(, LastCol - 1).Formula = "=SUM(B2:B" & c.Row - 1 & ")"


End Sub[/VBA]

Many Thanks for you help !! much appreciated.

[mdmackillop]

c.row -1 will exclude the Total Cars row. Omit the -1 if you wish to count it.