(Solved) – Receiving VBA 1004 Application-defined or object-defined error when running a macro

  • by
(solved)-–-receiving-vba-1004-application-defined-or-object-defined-error-when-running-a-macro

I’ve been working with macros and every time I try to run the following code I keep getting the

“Run-time error ‘1004’:”

and I can’t seem to find what is causing it. I will post the code below, if you guys have any ideas please let me know! This is my first post on the site so if I did something wrong please let me know and I’d be glad to fix it!

EDIT: The error is on line 14 where it says SolverSolve

Sub FD_Opto()
'
' FD_Opto Macro
'
' Keyboard Shortcut: Ctrl Shift T
'
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "500"
    Range("Y3").Select
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    ' The problem is on the next line
    SolverSolve
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Add2 Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N12:R22").Select
    Selection.Copy
    Sheets("FD lineups").Select
    Range("A1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("FD Optimizer").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    Range("Y34:Y35").Select
    Range("Y35").Activate
    Application.CutCopyMode = False
    Range("Y2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[20]C[-9]"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "=R[20]C[-9]-0.01"
    Range("Y3").Select
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Add2 Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N12:R22").Select
    Selection.Copy
    Sheets("FD lineups").Select
    Range("G1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("FD Optimizer").Select
    Application.CutCopyMode = False
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Add2 Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N12:R22").Select
    Selection.Copy
    Sheets("FD lineups").Select
    Range("M1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("FD Optimizer").Select
    Range("O23").Select
    Application.CutCopyMode = False
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Add2 Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N12:R22").Select
    Selection.Copy
    Sheets("FD lineups").Select
    Range("A13").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("FD Optimizer").Select
    Application.CutCopyMode = False
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Add2 Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N12:R22").Select
    Selection.Copy
    Sheets("FD lineups").Select
    Range("G13").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("FD Optimizer").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    Application.CutCopyMode = False
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$2:$G$201", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort.SortFields _
        .Add2 Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FD Optimizer").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N12:R22").Select
    Selection.Copy
    Sheets("FD lineups").Select
    Range("M13").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *