Share via

Excel VBA error. Complie error: Wrong number of arguments or invalid code assignment

Anonymous
2021-02-19T17:02:18+00:00

I have been using a recorded Excel macro in Excel to reformat text and columns.  It has worked perfectly for months until today when the error below is produced.  I'm not a coder but I think this must be something simple I'm missing.

Any suggestions?

Frank

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-02-19T17:19:14+00:00

    Hi Frank

    Would you mind posting the full code as well?

    Thanks

    Regards

    Jeovany

    0 comments
  2. Anonymous
    2021-02-19T18:05:37+00:00

    Hi Jeovany

    Thanks for your prompt reply.

    The code is pasted below.

    Frank

    Sub Ratings1()

    '

    ' Ratings1 Macro

    '

    '

    columns("W:W").Select

    Selection.Cut

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.SmallScroll ToRight:=33

columns("BO:BO").Select

ActiveSheet.Paste

    Range("A1").Select

    columns("W:W").Select

Selection.Delete Shift:=xlToLeft

Range("A1").Select

Range("A1").Select

columns("A:A").Select

Selection.NumberFormat = "h:mm;@"

Selection.ColumnWidth = 5

columns("B:B").Select

Selection.ColumnWidth = 5

columns("M:M").Select

Selection.Cut

columns("C:C").Select

Selection.Insert Shift:=xlToRight

columns("C:C").Select

Selection.ColumnWidth = 5

Range("A1").Select

ActiveCell.FormulaR1C1 = ""

Range("B1").Select

ActiveCell.FormulaR1C1 = ""

Range("C1").Select

ActiveCell.FormulaR1C1 = ""

Range("D1").Select

ActiveCell.FormulaR1C1 = ""

columns("D:D").Select

Selection.ColumnWidth = 20

columns("E:E").Select

Selection.Cut

columns("O:O").Select

Selection.Insert Shift:=xlToRight

columns("E:E").Select

Selection.Cut

columns("O:O").Select

Selection.Insert Shift:=xlToRight

columns("E:E").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.ColumnWidth = 5

columns("F:F").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("E1").Select

ActiveCell.FormulaR1C1 = "Res"

Range("F1").Select

ActiveCell.FormulaR1C1 = "BFSP"

columns("G:G").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("G1").Select

ActiveCell.FormulaR1C1 = "Rat"

Range("H1").Select

ActiveCell.FormulaR1C1 = "LR"

columns("I:I").Select

Selection.Cut

columns("S:S").Select

Selection.Insert Shift:=xlToRight

columns("I:I").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("I1").Select

ActiveCell.FormulaR1C1 = "Lr2"

Range("J1").Select

ActiveCell.FormulaR1C1 = "Lr3"

Range("H1").Select

ActiveCell.FormulaR1C1 = "Lr"

columns("L:L").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("AA:AA").Select

Selection.Cut

columns("L:L").Select

Selection.Insert Shift:=xlToRight

Selection.ColumnWidth = 5

Range("L1").Select

ActiveCell.FormulaR1C1 = "TC%"

columns("Z:Z").Select

Selection.Cut

columns("M:M").Select

Selection.Insert Shift:=xlToRight

Selection.ColumnWidth = 5

Range("M1").Select

ActiveCell.FormulaR1C1 = "JC%"

columns("AA:AA").Select

Selection.Cut

columns("O:O").Select

Selection.Insert Shift:=xlToRight

Selection.ColumnWidth = 5

Selection.ColumnWidth = 5

Range("O1").Select

ActiveCell.FormulaR1C1 = "T30"

columns("T:T").Select

Selection.Cut

columns("Q:Q").Select

Selection.Insert Shift:=xlToRight

columns("V:V").Select

Selection.Cut

columns("R:R").Select

Selection.Insert Shift:=xlToRight

columns("Q:R").Select

Selection.ColumnWidth = 20

Range("Q1").Select

ActiveCell.FormulaR1C1 = "T"

Range("R1").Select

ActiveCell.FormulaR1C1 = "J"

columns("AC:AC").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("AE:AE").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("AF:AF").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("AG:AG").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("AH:AH").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("AR:AR").Select

Selection.Cut

Range("AF1").Select

Selection.Insert Shift:=xlToRight

columns("AI:AI").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("AQ:AQ").Select

Selection.Cut

columns("AI:AI").Select

Selection.Insert Shift:=xlToRight

columns("AS:AS").Select

Selection.Cut

columns("AJ:AJ").Select

Selection.Insert Shift:=xlToRight

columns("AU:AU").Select

Selection.Cut

Range("AK1").Select

Selection.Insert Shift:=xlToRight

columns("AI:AK").Select

Selection.ColumnWidth = 10

Range("AI1").Select

ActiveCell.FormulaR1C1 = "LRrns"

Range("AJ1").Select

ActiveCell.FormulaR1C1 = "2LRns"

Range("AK1").Select

ActiveCell.FormulaR1C1 = "3LRns"

Range("AJ2").Select

Range("A1").Select

Range("H2").Select

ActiveCell.FormulaR1C1 = "=RC[21]/RC[27]"

Range("I2").Select

ActiveCell.FormulaR1C1 = "=RC[22]/RC[27]"

Range("J2").Select

ActiveCell.FormulaR1C1 = "=RC[23]/RC[27]"

columns("H:J").Select

Selection.NumberFormat = "0.0"

Range("H2:J2").Select

Selection.AutoFill Destination:=Range("H2:J297"), Type:=xlFillDefault

Range("H2:J297").Select

ActiveWindow.SmallScroll Down:=-396

Rows("2:2").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

columns("L:L").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, \_

    Formula1:="=14"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

    .Color = -16752384

    .TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 13561798

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

columns("M:M").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, \_

    Formula1:="=15"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

    .Color = -16752384

    .TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 13561798

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

columns("O:O").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, \_

    Formula1:="=15"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

    .Color = -16752384

    .TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 13561798

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("A1:R1").Select

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

Selection.Font.Bold = True

Rows("1:1").Select

Selection.RowHeight = 17

Range("G3").Select

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[3])\*5"

Range("G3").Select

Selection.AutoFill Destination:=Range("G3:G298"), Type:=xlFillDefault

Range("G3:G298").Select

ActiveWindow.SmallScroll Down:=-372

columns("G:G").Select

Selection.NumberFormat = "0.0"

columns("G:O").Select

With Selection

    .HorizontalAlignment = xlGeneral

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

columns("G:G").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("G3").Select

Rows("1:1").Select

With ActiveWindow

    .SplitColumn = 0

    .SplitRow = 1

End With

ActiveWindow.FreezePanes = True

Range("G3").Select

ActiveCell.FormulaR1C1 = "=IF(AND(RC[2]<5,RC[7]>19),""S"","" "")"

Range("G3").Select

Selection.AutoFill Destination:=Range("G3:G298"), Type:=xlFillDefault

Range("G3:G298").Select

ActiveWindow.SmallScroll Down:=-348

Range("G15").Select

ActiveWindow.SmallScroll Down:=-96

columns("G:G").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="Sel", \_

    TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

    .Color = -16752384

    .TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 13561798

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

With Selection

    .HorizontalAlignment = xlGeneral

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

columns("E:L").Select

With Selection

    .HorizontalAlignment = xlGeneral

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

columns("D:D").Select

Selection.Font.Bold = False

Selection.Font.Bold = True

columns("C:C").Select

With Selection

    .HorizontalAlignment = xlGeneral

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

Range("A1").Select

Range("P3").Select

ActiveCell.FormulaR1C1 = "=(AVERAGE(BB3:BD3)-AA3)/100"

Range("P4").Select

columns("P:P").Select

Selection.NumberFormat = "0.0"

Range("P1").Select

ActiveCell.FormulaR1C1 = "£k"

Range("P1").Select

Selection.Style = "Good"

Range("P3").Select

Selection.AutoFill Destination:=Range("P3:P290"), Type:=xlFillDefault

Range("P3:P290").Select

ActiveWindow.SmallScroll Down:=-372

Range("B1").Select

Range("G1").Select

ActiveCell.FormulaR1C1 = "Sel"

Range("B1").Select

Range("AE3").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Lookup!C[-30]:C[-29],2,FALSE)"

Range("AG3").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Lookup!C[-32]:C[-31],2,FALSE)"

Range("AI3").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Lookup!C[-34]:C[-33],2,FALSE)"

Range("AE3").Select

Selection.AutoFill Destination:=Range("AE3:AE290"), Type:=xlFillDefault

Range("AE3:AE290").Select

Range("AG3").Select

Selection.AutoFill Destination:=Range("AG3:AG286"), Type:=xlFillDefault

Range("AG3:AG286").Select

Range("AI3").Select

Selection.AutoFill Destination:=Range("AI3:AI284"), Type:=xlFillDefault

Range("AI3:AI284").Select

Range("A1").Select

columns("J:L").Select

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.249977111117893

End With

columns("I:I").Select

Selection.Font.Bold = False

Selection.Font.Bold = True

Range("A1").Select

columns("J:L").Select

Selection.ColumnWidth = 3

columns("M:M").Select

Selection.ColumnWidth = 2

columns("R:R").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

columns("R:T").Select

Selection.ColumnWidth = 3

Range("A1").Select

Range("R1").Select

ActiveCell.FormulaR1C1 = "C"

Range("S1").Select

ActiveCell.FormulaR1C1 = "D"

Range("T1").Select

ActiveCell.FormulaR1C1 = "CD"

columns("F:F").Select

Selection.NumberFormat = "0.0"

columns("AZ:BB").Select

Selection.NumberFormat = "0"

Range("R3").Select

ActiveCell.FormulaR1C1 = "=IF(RC[34]>1,""Y"","" "")"

Range("R3").Select

Selection.AutoFill Destination:=Range("R3:T3"), Type:=xlFillDefault

Range("R3:T3").Select

Range("R3:T3").Select

Selection.AutoFill Destination:=Range("R3:T290"), Type:=xlFillDefault

Range("R3:T290").Select

Application.WindowState = xlMaximized

columns("R:T").Select

Selection.FormatConditions.Delete

Range("R1").Select

Selection.Style = "Good"

Range("S1").Select

Selection.Style = "Good"

Range("T1").Select

Selection.Style = "Good"

Range("R3:T291").Select

ActiveWindow.SmallScroll Down:=-348

Selection.FormatConditions.Add Type:=xlTextString, String:="Y", \_

    TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

    .Color = -16752384

    .TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 13561798

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("A1").Select

columns("P:P").Select

Selection.Delete Shift:=xlToLeft

columns("I:I").Select

Selection.NumberFormat = "0.00"

columns("Q:Q").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

    .LineStyle = xlContinuous

    .ColorIndex = xlAutomatic

    .TintAndShade = 0

    .Weight = xlThin

End With

Selection.Borders(xlEdgeTop).LineStyle = xlNone

Selection.Borders(xlEdgeBottom).LineStyle = xlNone

Selection.Borders(xlEdgeRight).LineStyle = xlNone

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

columns("Q:Q").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.ColumnWidth = 3

Selection.ColumnWidth = 2

Range("A1").Select

columns("J:L").Select

Selection.Cut

Range("AQ1").Select

ActiveSheet.Paste

columns("W:AO").Select

Selection.ColumnWidth = 6

columns("AU:BC").Select

Selection.ColumnWidth = 6

columns("BC:BC").Select

Selection.ColumnWidth = 12

Range("J1").Select

ActiveCell.FormulaR1C1 = "Bet"

Range("K1").Select

ActiveCell.FormulaR1C1 = "PL"

Range("L1").Select

ActiveCell.FormulaR1C1 = "Rnk"

columns("J:L").Select

Selection.ColumnWidth = 4

columns("J:J").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("A1").Select

columns("J:N").Select

With Selection

    .HorizontalAlignment = xlGeneral

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

Range("A1").Select

 columns("K:K").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="Y", \_

    TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 65535

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("A1").Select

columns("I:Q").Select

Selection.ColumnWidth = 5

Range("M1").Select

Selection.ClearContents

Range("J1").Select

ActiveCell.FormulaR1C1 = "Rnk"

Range("M1").Select

ActiveCell.FormulaR1C1 = "PL"

Range("L1").Select

ActiveCell.FormulaR1C1 = "Wns"

Range("A1").Select

columns("J:J").Select

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.349986266670736

End With

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.499984740745262

End With

Range("J1").Select

With Selection.Font

    .ColorIndex = xlAutomatic

    .TintAndShade = 0

End With

Range("A1").Select

columns("H:H").Select

Selection.ColumnWidth = 2

columns("J:J").Select

Selection.ColumnWidth = 3

columns("K:M").Select

Selection.ColumnWidth = 4

columns("N:N").Select

Selection.ColumnWidth = 2

columns("J:J").Select

Selection.NumberFormat = "0"

Range("A1").Select

    columns("C:C").Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= \_

    "=$AD1=""Handicap"""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .ThemeColor = xlThemeColorAccent3

    .TintAndShade = 0.799981688894314

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("A1").Select

columns("I:I").Select

Selection.Cut

columns("E:E").Select

Selection.Insert Shift:=xlToRight

columns("H:H").Select

Selection.Cut

columns("F:F").Select

Selection.Insert Shift:=xlToRight

columns("H:H").Select

Selection.Cut

columns("G:G").Select

Selection.Insert Shift:=xlToRight

columns("J:J").Select

Selection.Cut

columns("N:N").Select

Selection.Insert Shift:=xlToRight

columns("N:N").Select

Selection.ColumnWidth = 2

columns("M:M").Select

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.149998474074526

End With

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.149998474074526

End With

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.149998474074526

End With

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.149998474074526

End With

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.349986266670736

End With

columns("F:F").Select

Selection.ColumnWidth = 4

columns("E:E").Select

Selection.ColumnWidth = 6

columns("G:H").Select

Selection.ColumnWidth = 4

columns("M:M").Select

Selection.Cut

columns("N:N").Select

Selection.Insert Shift:=xlToRight

columns("N:N").Select

Selection.Delete Shift:=xlToLeft

Range("A1").Select

columns("E:E").Select

Selection.ColumnWidth = 6

columns("F:H").Select

Selection.ColumnWidth = 4

columns("J:L").Select

Selection.ColumnWidth = 4

columns("M:M").Select

Selection.ColumnWidth = 2

Range("M1").Select

ActiveCell.FormulaR1C1 = "R"

columns("K:K").Select

Selection.ColumnWidth = 5

columns("K:L").Select

With Selection.Font

    .ColorIndex = xlAutomatic

    .TintAndShade = 0

End With

columns("Q:Q").Select

Selection.ColumnWidth = 2

Range("A1").Select

columns("M:M").Select

Selection.Cut

columns("U:U").Select

Selection.Insert Shift:=xlToRight

Range("U1").Select

ActiveCell.FormulaR1C1 = "G"

Range("U1").Select

Selection.Font.Bold = False

With Selection.Font

    .ColorIndex = xlAutomatic

    .TintAndShade = 0

End With

columns("T:T").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("T1").Select

ActiveCell.FormulaR1C1 = "G"

Range("T3").Select

ActiveCell.FormulaR1C1 = "=IF(RC[28]>1,""Y"","" "")"

Range("T3").Select

Selection.AutoFill Destination:=Range("T3:T296"), Type:=xlFillDefault

Range("T3:T296").Select

    Range("A1").Select

     columns("U:U").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.ColumnWidth = 3

Range("W1").Select

ActiveCell.FormulaR1C1 = "T"

Range("U1").Select

ActiveCell.FormulaR1C1 = "£"

Range("U3").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[30]:RC[32])/1000"

columns("U:U").Select

Selection.NumberFormat = "0"

Range("U3").Select

Selection.AutoFill Destination:=Range("U3:U118"), Type:=xlFillDefault

Range("U3:U118").Select

ActiveWindow.SmallScroll Down:=-9

Range("U118:U283").Select

ActiveWindow.SmallScroll Down:=-249

Range("A2").Select

columns("U:U").Select

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = -0.349986266670736

End With

With Selection.Interior

    .Pattern = xlNone

    .TintAndShade = 0

    .PatternTintAndShade = 0

End With

Range("A1").Select

columns("H:H").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("H1").Select

ActiveCell.FormulaR1C1 = "BF2"

Range("I1").Select

ActiveCell.FormulaR1C1 = "BF1"

Range("G1").Select

ActiveCell.FormulaR1C1 = "BFSP"

columns("AH:AH").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("AH3").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],LookupHcp!C[-33]:C[-32],2,FALSE)"

Range("AH3").Select

Selection.AutoFill Destination:=Range("AH3:AH288"), Type:=xlFillDefault

Range("AH3:AH288").Select

With Selection

    .HorizontalAlignment = xlGeneral

    .VerticalAlignment = xlCenter

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlCenter

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

Range("A1").Select

columns("C:C").Select

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= \_

    "=$AH1=""Yes"""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .ThemeColor = xlThemeColorAccent3

    .TintAndShade = 0.799981688894314

End With

Selection.FormatConditions(1).StopIfTrue = False

columns("K:K").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="?", \_

    TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

    .Color = -16383844

    .TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 13551615

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("A1").Select

columns("AC:AT").Select

Selection.ColumnWidth = 4

columns("AU:AU").Select

Selection.ColumnWidth = 2

columns("AY:AY").Select

Selection.ColumnWidth = 2

columns("AZ:BG").Select

Selection.ColumnWidth = 4

Range("A1").Select

columns("BI:BI").Select

Selection.ColumnWidth = 2

Range("BJ1").Select

Selection.NumberFormat = "0"

ActiveCell.FormulaR1C1 = "Month"

Range("BK1").Select

Selection.NumberFormat = "0"

ActiveCell.FormulaR1C1 = "Chase"

Range("BL1").Select

Selection.NumberFormat = "0"

ActiveCell.FormulaR1C1 = "Hurdle"

Range("BJ2").Select

Range("A1").Select

Range("BJ3").Select

ActiveCell.FormulaR1C1 = "=TEXT(RC[-2], ""mmm-yyy"")"

Range("BK3").Select

ActiveCell.FormulaR1C1 = \_

    "=IF(ISNUMBER(SEARCH(""Chase"",RC[-30])),""Yes"","""")"

Range("BL3").Select

ActiveCell.FormulaR1C1 = \_

    "=IF(ISNUMBER(SEARCH(""Hurdle"",RC[-31])),""Yes"","""")"

Range("BJ3:BL3").Select

Selection.AutoFill Destination:=Range("BJ3:BL298"), Type:=xlFillDefault

Range("BJ3:BL298").Select

Range("A1").Select

columns("O:O").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("O1").Select

ActiveCell.FormulaR1C1 = "BFp"

Range("A1").Select

columns("S:W").Select

Selection.ColumnWidth = 4

Range("S3").Select

ActiveCell.FormulaR1C1 = "=IF(RC[13]>119, ""??"", IF(RC[13]>89, ""?"", "" ""))"

Range("S3").Select

Selection.AutoFill Destination:=Range("S3:S298"), Type:=xlFillDefault

Range("S3:S298").Select

Selection.FormatConditions.Delete

Range("R1").Select

Selection.AutoFill Destination:=Range("R1:S1"), Type:=xlFillDefault

Range("R1:S1").Select

Range("S1").Select

ActiveCell.FormulaR1C1 = "Fit"

Range("A1").Select

Range("K3").Select

ActiveCell.FormulaR1C1 = "=IF(COUNTBLANK(RC[38]:RC[40])>0,""?"", """")"

Range("K3").Select

Selection.AutoFill Destination:=Range("K3:K182"), Type:=xlFillDefault

Range("K3:K182").Select

Range("Z3:Z297").Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= \_

    "=Z3=VLOOKUP(Z3,Trainers!A:A,1,0)"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .ThemeColor = xlThemeColorAccent3

    .TintAndShade = 0.799981688894314

End With

Selection.FormatConditions(1).StopIfTrue = False

columns("G:O").Select

Selection.ColumnWidth = 5

columns("K:K").Select

Selection.ColumnWidth = 3

Range("A1").Select

columns("J:J").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

    .LineStyle = xlContinuous

    .ColorIndex = xlAutomatic

    .TintAndShade = 0

    .Weight = xlThin

End With

Selection.Borders(xlEdgeTop).LineStyle = xlNone

Selection.Borders(xlEdgeBottom).LineStyle = xlNone

With Selection.Borders(xlEdgeRight)

    .LineStyle = xlContinuous

    .ColorIndex = xlAutomatic

    .TintAndShade = 0

    .Weight = xlThin

End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("A1").Select

columns("G:G").Select

Selection.Delete Shift:=xlToLeft

columns("N:N").Select

Selection.Delete Shift:=xlToLeft

Range("A1").Select

columns("Q:Q").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="??", \_

    TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

    .Color = -16383844

    .TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 13551615

    .TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("A1").Select

columns("AZ:AZ").Select

Selection.Copy

columns("V:V").Select

Selection.Insert Shift:=xlToRight

Range("V1").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "W%"

columns("V:V").Select

Selection.ColumnWidth = 4

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = False

End With

With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlign
    0 comments
  3. Anonymous
    2021-02-19T21:59:42+00:00

    Problem sort of solved.

    I had an idea to copy the macro text to my laptop and it runs perfectly!

    So new question how do I fix my desktop?

    FC

    0 comments
  4. Anonymous
    2021-02-20T09:25:01+00:00

    Hi

    You might have to check the running Excel version on your Desktop PC

    Also, macros in Excel online do not work/run. In case, you are trying to do so.

    When I pasted your code I found the following in a few instances.

    Image

    I removed the extra line feet space and the macro worked OK

    Image

    Regarding your code

    I hope you'll agree with me that it is a staggering amount of recorded macros piled/merged/combined

    some of them twice and trice copied likely to fix and refix the sheet formatting.

    I don't blame you, or criticizing your work, of course, don't get me wrong, I would do the same in your position

    I just noticed the macro final goal is to format a sheet

    If you share with us a link to a copy of your file with both, the original sheet before the formatting and the sheet with the desired formatting (after running the macro).

    I might do some work over the weekend on your file to optimized the macro

    Feel free to respond

    Anyway, I'm glad you found the solution to your original problem

    Regards

    Jeovany

    0 comments
  5. Anonymous
    2021-02-20T17:04:38+00:00

    Hi Jeovany 

    Many thanks for your input.

    This project started off as a small job within my skills but grew steadily over about a year until this episode. I've had a look at my source data and final report format and realised the process could be a lot simpler. I still have all my notes from this project and will create a new simpler version.

    I probably should learn how to build a database as this would be a safer way to hold the data and produce the final report.

    Regards

    Frank

    0 comments