Sub Sum

  • Thread starter Thread starter Buddy
  • Start date Start date
B

Buddy

Sub Sum()
Range("Rows(2)" & Range("B:AA" & Rows.Count).End(xlUp).Row).Formula =
"=SUM(B3:B15)"
End Sub

Here is my current macro. I am aiming to get every Column in Row 2 starting
in B2 and ending in AA2 to put the formulas below inside. If you can help I
would appreciate it.

In B3 I am want to put this formula =SUM(B3:B15)
In C3 I am want to put this formula =SUM(C3:C15)
In D3 I am want to put this formula =SUM(D3:D15)
Continued...
In AA3 I am want to put this formula =SUM(AA3:AA15)
 
Buddy,
Try:

Range("B2:AA2").Select
Selection.FillRight

This will do the same as Ctrl+R from the keyboard
 
In B3 I am want to put this formula =SUM(B3:B15)
Click to expand...

That'd give you a circular reference, I guess you mean you want the formula
in B2 etc

Range("B2:AA2").Formula = "=SUM(B3:B15)"

Regards,
Peter T
 
Sub Sum()


LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

Range("A" & (LastRow + 2)) = "Total"
Range("B" & (LastRow + 2)).Formula = _
"=SUM(B2:B" & LastRow & ")"

Range("B" & (LastRow + 2)).Copy _
Destination:=Range(Range("B" & (LastRow + 2)), Cells((LastRow + 2),
LastColumn))


End Sub
 
Peter,
Yes, the function would need to start from the first cell that has the
formula, but the fomula would need to be in the cell B2 with with a formula
=Sum(B3:B15)

Then select B2:AA2, and selection.rightfill.
 
Thank you for the tip. I appreciate your input.

Peter T said:
That'd give you a circular reference, I guess you mean you want the formula
in B2 etc

Range("B2:AA2").Formula = "=SUM(B3:B15)"

Regards,
Peter T
Click to expand...
 
Remarkable. Thank you very much for your help Sir. I am very thankful for
your direction. It works very well.
 
Chris, couple of points. If you want to use autifill there's no need to use
Select -

Range("B2").Formula = "=Sum(B3:B15)"
Range("B2").AutoFill Range("B2:AA2")

However the way I suggested previously is more efficient and avoids
triggering a change event, simply

Range("B2:AA2").Formula = "=Sum(B3:B15)"

Regards,
Peter T
 
Back
Top