I am having a hard time inserting this formula into my macro, keep getting a debuggin error.
My Macro is this
Sub HWI_INV_CODES()
'
' HWI_INV_CODES Macro
'
'
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$BX$29470").AutoFilter Field:=6, Criteria1:="HWI"
Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.SmallScroll ToRight:=2
ActiveSheet.Range("$A$1:$BX$29470").AutoFilter Field:=19
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Windows("ZPRGSUMST1_000003279.CSV").Activate
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Range("B1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Columns("G:G").Select
Range("G26238").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("B1").Select
ActiveSheet.Paste
Columns("C:C").Select
Columns("B:B").EntireColumn.AutoFit
Range("C1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
ActiveWindow.SmallScroll Down:=-24
Range("E25519").Select
Selection.End(xlUp).Select
Range("G1").Select
ActiveWindow.SmallScroll ToRight:=5
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("C1").Select
ActiveSheet.Paste
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
ActiveWindow.LargeScroll ToRight:=-1
Columns("C:D").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("D1").Select
ActiveSheet.Paste
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Range("F1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("F1").Select
ActiveSheet.Paste
Range("G1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
ActiveWindow.SmallScroll ToRight:=7
Columns("S:S").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("G1").Select
ActiveSheet.Paste
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Previous Inventory Code"
Range("H1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I1").Select
ActiveCell.FormulaR1C1 = "Change From Last Month"
Rows("1:1").Select
Range("B1").Activate
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-1],""No"",""Yes"")"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I363")
Range("I2:I363").Select
ActiveWindow.ScrollRow = 1493
ActiveWindow.ScrollRow = 8956
ActiveWindow.ScrollRow = 14926
ActiveWindow.ScrollRow = 110451
ActiveWindow.ScrollRow = 470163
ActiveWindow.ScrollRow = 570166
ActiveWindow.ScrollRow = 864204
ActiveWindow.ScrollRow = 911967
ActiveWindow.ScrollRow = 656736
ActiveWindow.ScrollRow = 340309
ActiveWindow.ScrollRow = 17911
ActiveWindow.ScrollRow = 1
Range("J1").Select
ActiveCell.FormulaR1C1 = "Active Item"
Range("K1").Select
ActiveCell.FormulaR1C1 = "$$ Available"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Units Available"
Range("K2").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Range("BE80").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "$#,##0"
Range("L2").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Range("BH26836").Select
Selection.End(xlUp).Select
Range("AW80").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
Range("L2").Select
ActiveSheet.Paste
Range("L1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Cells.Select
Range("F1").Activate
Cells.EntireColumn.AutoFit
Range("J2").Select
End Sub
and the formula I need to insert at the end is
=IF(K2=5,"Mature Item",IF(K2=1,"New item in DC-PO's Placed",IF(K2=0,"New Item in DC-No PO's Placed",IF(K2=2,"PO's received-oldest PO <1 year old",IF(K2=3,"Samples",IF(K2=6,"Slow Moving Risk Item",IF(K2=8,"Slow Moving Liability Item",IF(K2=7,"Item Discontinued with Open PO's",IF(K2=10,"Item Closed in location-item is still active",IF(K2=15,"Item Discontinued",IF(K2=9,"Dummy Item",IF(K2=18,"DWO-Discount Level 3","No"))))))))))))
Please help as I am missing something here, thank you
My Macro is this
Sub HWI_INV_CODES()
'
' HWI_INV_CODES Macro
'
'
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$BX$29470").AutoFilter Field:=6, Criteria1:="HWI"
Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.SmallScroll ToRight:=2
ActiveSheet.Range("$A$1:$BX$29470").AutoFilter Field:=19
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Windows("ZPRGSUMST1_000003279.CSV").Activate
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Range("B1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Columns("G:G").Select
Range("G26238").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("B1").Select
ActiveSheet.Paste
Columns("C:C").Select
Columns("B:B").EntireColumn.AutoFit
Range("C1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
ActiveWindow.SmallScroll Down:=-24
Range("E25519").Select
Selection.End(xlUp).Select
Range("G1").Select
ActiveWindow.SmallScroll ToRight:=5
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("C1").Select
ActiveSheet.Paste
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
ActiveWindow.LargeScroll ToRight:=-1
Columns("C:D").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("D1").Select
ActiveSheet.Paste
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Range("F1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("F1").Select
ActiveSheet.Paste
Range("G1").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
ActiveWindow.SmallScroll ToRight:=7
Columns("S:S").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("G1").Select
ActiveSheet.Paste
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Previous Inventory Code"
Range("H1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I1").Select
ActiveCell.FormulaR1C1 = "Change From Last Month"
Rows("1:1").Select
Range("B1").Activate
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-1],""No"",""Yes"")"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I363")
Range("I2:I363").Select
ActiveWindow.ScrollRow = 1493
ActiveWindow.ScrollRow = 8956
ActiveWindow.ScrollRow = 14926
ActiveWindow.ScrollRow = 110451
ActiveWindow.ScrollRow = 470163
ActiveWindow.ScrollRow = 570166
ActiveWindow.ScrollRow = 864204
ActiveWindow.ScrollRow = 911967
ActiveWindow.ScrollRow = 656736
ActiveWindow.ScrollRow = 340309
ActiveWindow.ScrollRow = 17911
ActiveWindow.ScrollRow = 1
Range("J1").Select
ActiveCell.FormulaR1C1 = "Active Item"
Range("K1").Select
ActiveCell.FormulaR1C1 = "$$ Available"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Units Available"
Range("K2").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Range("BE80").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "$#,##0"
Range("L2").Select
Windows("ZPRGSUMST1_000003279.CSV").Activate
Range("BH26836").Select
Selection.End(xlUp).Select
Range("AW80").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
Range("L2").Select
ActiveSheet.Paste
Range("L1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Cells.Select
Range("F1").Activate
Cells.EntireColumn.AutoFit
Range("J2").Select
End Sub
and the formula I need to insert at the end is
=IF(K2=5,"Mature Item",IF(K2=1,"New item in DC-PO's Placed",IF(K2=0,"New Item in DC-No PO's Placed",IF(K2=2,"PO's received-oldest PO <1 year old",IF(K2=3,"Samples",IF(K2=6,"Slow Moving Risk Item",IF(K2=8,"Slow Moving Liability Item",IF(K2=7,"Item Discontinued with Open PO's",IF(K2=10,"Item Closed in location-item is still active",IF(K2=15,"Item Discontinued",IF(K2=9,"Dummy Item",IF(K2=18,"DWO-Discount Level 3","No"))))))))))))
Please help as I am missing something here, thank you
