PDA

View Full Version : Solved: Summing range and copying accross



satyen
03-20-2010, 07:40 AM
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...

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

mdmackillop
03-20-2010, 07:59 AM
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



Cells(Columns.Count, 15)


Remember to put Columns.Count as the second argument

satyen
03-20-2010, 08:15 AM
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.

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

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

mdmackillop
03-20-2010, 08:51 AM
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

satyen
03-20-2010, 09:06 AM
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.

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

Many Thanks for you help !! much appreciated.

mdmackillop
03-20-2010, 09:13 AM
c.row -1 will exclude the Total Cars row. Omit the -1 if you wish to count it.