Inserting a Formula into my macro

[sjuhl99]

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

 

[mdorey]

Range("cell that you want the formula in").Value = "=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""))))))))))))"

you need to double the " " every time

 

[sjuhl99]

worked perfect, thank you very much. Another quick question, I am referencing this csv file Windows("ZPRGSUMST1_000003279.CSV").Activate however a new fresh file gets dropped into the same location everyday, for example tomorrow's version will be named ZPRGSUMST1_000003280.CSV. How do I have my Macro always select the newest version, or is that not possible?

 

[mdorey]

Im not at all an expert in VBA however if you add this at the beginning of your code it could solve part of your problem

Dim myValue As Variant
myValue = InputBox("Enter the remaining of the file name:" & vbNewLine & _
"[COLOR=#333333]ZPRGSUMST1_"[/COLOR])

Then replace Windows("ZPRGSUMST1_000003279.CSV").Activate

for

Windows("ZPRGSUMST1_" & myValue & ".CSV").Activate

This can "help" you with that... and you just insert the number of the file that you want to open

 

[Fluff]

will the csv file be the active(ie visible) file when you run the macro?
if so add

    Dim Wb1 As Workbook
    
    Set Wb1 = ActiveWorkbook

to the top of your code & then replace every occurance of

[COLOR=#333333]Windows("ZPRGSUMST1_000003279.CSV").Activate[/COLOR]

with

 Wb1.Activate

 

[mdorey]

Fluff that part he already have... the problem is to open new name every day... normally plus 1 value every day... today is
ZPRGSUMST1_000003279.CSV tomorow will be ZPRGSUMST1_000003280.CSV

 

[Fluff]

Your macro is not opening that file, so when you run the macro tomorrow, will file ZPRGSUMST1_000003280.CSV be open.

 

[mdorey]

worked perfect, thank you very much. Another quick question, I am referencing this csv file Windows("ZPRGSUMST1_000003279.CSV").Activate however a new fresh file gets dropped into the same location everyday, for example tomorrow's version will benamed ZPRGSUMST1_000003280.CSV. How do I have my Macro always select the newest version, or is that not possible?

however... if until now you didn't solve the problem i think you will need to open a new thread

 

[Fluff]

however... if until now you didn't solve the problem i think you will need to open a new thread

There is no need to start a new thread.
IMHO it's best to keep this all on one thread as it's related.

 

[sjuhl99]

Thank you all for your help, yes the new csv will be open every time, that is the base file. It worked and I am good to go, really appreciate your help!