Creating Macro's to add formulas/formatting

[MarcG]

I am trying to create a macro that will take me to the next available cell in any particular column. At this point I would like to add a formula to sum the column and format it with font/border.</SPAN>
What is the best way to accomplish this?</SPAN>
Thank you.</SPAN>

 

[HalfAce]

Well, (assuming you're working with column A), for the sum formula you can use something like this.

Sub AddSumFormula_Click()
Dim NxtRw As Long
NxtRw = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(NxtRw, "A").Formula = "=SUM(A1:A" & NxtRw - 1 & ")"
End Sub

What font & border do you want to format it with?

 

[MarcG]

I tried to copy the code into my existing macro and now it does not work. The formatting of the cell would be Arial 8 bolded with top and double bottom border. I would pressume that if I wanted to do the same thing for a different column I wuold relpace the "A" with that column such as "B" or "Z"

 

[HalfAce]

OK, by itself, this should do everything the original question asks for:

Sub AddSumFormula_Click()
Dim NxtRw As Long
NxtRw = Cells(Rows.Count, "A").End(xlUp).Row + 1
With Cells(NxtRw, "A")
  .Formula = "=SUM(A1:A" & NxtRw - 1 & ")"
  .Font.Name = "Arial"
  .Font.Size = 8
  .Font.Bold = True
  .Borders(xlEdgeTop).LineStyle = xlContinuous
  .Borders(xlEdgeTop).Weight = xlThin
  .Borders(xlEdgeBottom).LineStyle = xlDouble
  .Borders(xlEdgeBottom).Weight = xlThick
End With
End Sub

I would presume that if I wanted to do the same thing for a different column I would relpace the "A" with that column such as "B" or "Z"

Yes, simply replace all instances of "A" with whatever column you want.

I tried to copy the code into my existing macro and now it does not work.

I'm afraid I can't help much with this part without seeing your existing code.

 

[MarcG]

Here is a piece of the code. I was able to record the macro to do most everything except for the items above.

Sheets("Monthly").Select
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("C:G").Select
Selection.Style = "Comma"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select