Share via

Format table & insert symbol through vba

MA 101 Reputation points
2021-09-26T10:57:15.253+00:00

Hi: I was wondering if I could some assistance please with excel vba coding. Every month I run a report in our BI system and export to excel. I want to format the table through macro so I don't have to repeat this task every month. When I try to insert (e.g wingdings) symbols while macro recorder is on, the script doesn't display this step properly. Not sure why. Please see below the file link for my desired output.
Greatly appreciated for any assistance on this.

https://1drv.ms/x/s!Amc8fiGpDxekjFBiInSE2wWB2p5z?e=JlF1Qa

Many Thanks

Microsoft 365 and Office | Excel | For business | Windows
Developer technologies | Visual Basic for Applications
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MA 101 Reputation points
    2021-09-26T23:07:51.893+00:00

    Hi Please see below the lines where tagged them as 1----

    Sub test()
    '
    ' test Macro
    ' Format table
    '
    ' Keyboard Shortcut: Ctrl+z
    '
    Rows("1:2").Select
    Range("M1").Activate
    Selection.Delete Shift:=xlUp
    Columns("A:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Selection.ClearContents
    Range("A1:A5").Select
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Range("A1:G1").Select
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Range("A1:G5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    Range("A1:G1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Month Actual"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Month Budget"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Inv_Gain: (In Thousands)"
    Range("I3").Select
    Columns("A:A").ColumnWidth = 22.45
    Range("A1:B1").Select
    Range("B1").Activate
    Columns("A:A").ColumnWidth = 22
    Rows("1:1").RowHeight = 27.5
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "¦??"
    Range("A3").Select
    1---- ActiveCell.FormulaR1C1 = "¦ Inventory Value Adjustment Gain"
    Range("A4").Select
    1---- ActiveCell.FormulaR1C1 = "¦ Gain-Price Difference"
    Range("A5").Select
    1---- ActiveCell.FormulaR1C1 = "¦ Gain-Inventory Variance"
    Range("A3:A5").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 2
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range("A3:A5").Select
    Selection.Font.Italic = True
    Columns("A:A").ColumnWidth = 24.45
    Columns("A:A").ColumnWidth = 24.73
    Range("A2:G5").Select
    With Selection
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("A:A").ColumnWidth = 26.91
    Columns("A:A").ColumnWidth = 30.73
    Columns("A:A").ColumnWidth = 28.45
    Range("A2").Select
    Selection.Font.Bold = True
    Range("B2:C5,E2:F5").Select
    Range("E2").Activate
    Selection.NumberFormat = "#,##0 ; (#.00,)K"
    Range("D2:D5,G2:G5").Select
    Range("G2").Activate
    1----Selection.NumberFormat = "[Red]#.00,"" ?"" ;[Color10] (#.00,)"" ?
    Range("B2:G5").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range("G2").Select
    Columns("G:G").ColumnWidth = 13.91
    Columns("E:E").ColumnWidth = 12.45
    Columns("D:D").ColumnWidth = 13.55
    Columns("D:D").ColumnWidth = 12.09
    Range("B1:G1").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("C:C").ColumnWidth = 10.91
    Range("B1").Select
    End Sub

    Was this answer helpful?

    0 comments
  2. MA 101 Reputation points
    2021-09-26T20:28:22.247+00:00

    Hi: Thanks for your reply. Please see the code below:

    Sub test()
    '
    ' test Macro
    ' Format table
    '
    ' Keyboard Shortcut: Ctrl+z
    '
    Rows("1:2").Select
    Range("M1").Activate
    Selection.Delete Shift:=xlUp
    Columns("A:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Selection.ClearContents
    Range("A1:A5").Select
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Range("A1:G1").Select
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Range("A1:G5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    Range("A1:G1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Month Actual"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Month Budget"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Inv_Gain: (In Thousands)"
    Range("I3").Select
    Columns("A:A").ColumnWidth = 22.45
    Range("A1:B1").Select
    Range("B1").Activate
    Columns("A:A").ColumnWidth = 22
    Rows("1:1").RowHeight = 27.5
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "¦??"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "¦ Inventory Value Adjustment Gain"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "¦ Gain-Price Difference"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "¦ Gain-Inventory Variance"
    Range("A3:A5").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 2
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range("A3:A5").Select
    Selection.Font.Italic = True
    Columns("A:A").ColumnWidth = 24.45
    Columns("A:A").ColumnWidth = 24.73
    Range("A2:G5").Select
    With Selection
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("A:A").ColumnWidth = 26.91
    Columns("A:A").ColumnWidth = 30.73
    Columns("A:A").ColumnWidth = 28.45
    Range("A2").Select
    Selection.Font.Bold = True
    Range("B2:C5,E2:F5").Select
    Range("E2").Activate
    Selection.NumberFormat = "#,##0 ; (#.00,)K"
    Range("D2:D5,G2:G5").Select
    Range("G2").Activate
    Selection.NumberFormat = "[Red]#.00,"" ?"" ;[Color10] (#.00,)"" ?"""
    Range("B2:G5").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range("G2").Select
    Columns("G:G").ColumnWidth = 13.91
    Columns("E:E").ColumnWidth = 12.45
    Columns("D:D").ColumnWidth = 13.55
    Columns("D:D").ColumnWidth = 12.09
    Range("B1:G1").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("C:C").ColumnWidth = 10.91
    Range("B1").Select
    End Sub

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.