Creating Macro's to add formulas/formatting

MarcG

New Member
Joined
Feb 28, 2008
Messages
9
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>
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Well, (assuming you're working with column A), for the sum formula you can use something like this.
Code:
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

New Member
Joined
Feb 28, 2008
Messages
9
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
OK, by itself, this should do everything the original question asks for:
Code:
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

New Member
Joined
Feb 28, 2008
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,078
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top