i recorded this macro, i have no clue how to write these formula's in vb. so recording is my only current option. this macro is very long, is the a way to clean it up so it will run more efficiently?
Sub Sales_Analysis()
ChDir "Z:\jeffw"
Workbooks.OpenText FileName:="Z:\jeffw\CUST.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(11 _
, 2), Array(43, 2))
Workbooks.Add
Windows("CUST.TXT").Activate
Columns("A:C").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("K1").Select
Windows("CUST.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\CONTRACT.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(13, 2))
Columns("A:C").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("O1").Select
Windows("CONTRACT.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\ALL.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=6
Range("O4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ALL"
Range("T1").Select
Windows("ALL.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\CARBOLOY.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("T4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("Y1").Select
Windows("CARBOLOY.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\CLEVELAND.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Book2").Activate
ActiveSheet.Paste
Range("Y4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CLEVELAND"
Range("AD1").Select
Windows("CLEVELAND.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\GREENFIELD.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AD4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "GREENFIELD"
Range("AI1").Select
Windows("GREENFIELD.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\MARSHALL.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AI4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "MARSHALL"
Range("AN1").Select
Windows("MARSHALL.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\SGS.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Book2").Activate
ActiveSheet.Paste
Range("AN4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "SGS"
Range("AS1").Select
Windows("SGS.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\WELDON.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AS4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "WELDON"
Range("AX1").Select
Windows("WELDON.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\YG1.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AX4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "YG1"
Range("A1").Select
Windows("YG1.TXT").Activate
ActiveWindow.Close
ChDir "C:\Documents and Settings\jeff ward\My Documents\SALES ANALYSIS"
Columns("A:A").Select
Selection.NumberFormat = "@"
Range("A1").Select
ActiveWorkbook.Save
Range("N1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]&""@""&RC[-2]"
Range("N2").Select
Columns("N:N").ColumnWidth = 14.14
Range("N1:N10000").Select
Selection.FillDown
ActiveWindow.ScrollRow = 1
Columns("K:N").Select
Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Key2:=Range("N1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("J1").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "SALES-ANALYSIS"
Sheets("DATA").Select
Sheets("DATA").Move After:=Sheets(2)
Sheets("SALES-ANALYSIS").Select
Columns("B:B").Select
Selection.ColumnWidth = 12.43
Columns("C:D").Select
Selection.ColumnWidth = 10.14
Range("B2").Select
ActiveCell.FormulaR1C1 = "SLSP"
Range("C5").Select
ActiveCell.FormulaR1C1 = "24 mo total"
Range("D5").Select
ActiveCell.FormulaR1C1 = "12 mo total"
Range("E5").Select
ActiveCell.FormulaR1C1 = "6 mo total"
Range("B6").Select
ActiveCell.FormulaR1C1 = "TOTAL"
Range("B7").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("B8").Select
ActiveCell.FormulaR1C1 = "CLEVELAND"
Range("B9").Select
ActiveCell.FormulaR1C1 = "GREENFIELD"
Range("B10").Select
ActiveCell.FormulaR1C1 = "MARSHALL"
Range("B11").Select
ActiveCell.FormulaR1C1 = "WELDON"
Range("B12").Select
ActiveCell.FormulaR1C1 = "YG1"
Range("B13").Select
ActiveCell.FormulaR1C1 = "SGS"
Range("A1:B1").Select
Selection.Font.Bold = True
Range("A16").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "CURRENT CONTRACTS"
Rows("17:18").Select
Selection.Font.Bold = True
Range("A17").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("A18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("B18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("C17").Select
ActiveCell.FormulaR1C1 = "CLEVELAND"
Range("C18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("D18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("E17").Select
ActiveCell.FormulaR1C1 = "GREENFIELD"
Range("E18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("F18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("G17").Select
ActiveCell.FormulaR1C1 = "MARSHALL"
Range("G18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("H18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("I17").Select
ActiveCell.FormulaR1C1 = "SGS"
Range("I18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("J18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("K17").Select
ActiveCell.FormulaR1C1 = "WELDON"
Range("K18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("L18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("M17").Select
ActiveCell.FormulaR1C1 = "YG1"
Range("M18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("N18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],DATA!C[-1]:C,2,0)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C[-2],DATA!C[-2]:C,3,0)"
Range("C3").Select
Range("A1").Select
Selection.NumberFormat = "@"
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-5]C[-2],DATA!C[12]:C[13],2,0))),0,GETV())"
Range("C7").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-6]C[-2],DATA!C[17]:C[18],2,0))),0,GETV())"
Range("C8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-7]C[-2],DATA!C[22]:C[23],2,0))),0,GETV())"
Range("C9").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-8]C[-2],DATA!C[27]:C[28],2,0))),0,GETV())"
Range("C10").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-9]C[-2],DATA!C[32]:C[33],2,0))),0,GETV())"
Range("C11").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-10]C[-2],DATA!C[42]:C[43],2,0))),0,GETV())"
Range("C12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-11]C[-2],DATA!C[47]:C[48],2,0))),0,GETV())"
Range("C13").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-12]C[-2],DATA!C[37]:C[38],2,0))),0,GETV())"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-5]C[-3],DATA!C[11]:C[13],3,0))),0,GETV())"
Range("D7").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-6]C[-3],DATA!C[16]:C[18],3,0))),0,GETV())"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-7]C[-3],DATA!C[21]:C[23],3,0))),0,GETV())"
Range("D9").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-8]C[-3],DATA!C[26]:C[28],3,0))),0,GETV())"
Range("D10").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-9]C[-3],DATA!C[31]:C[33],3,0))),0,GETV())"
Range("D11").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-10]C[-3],DATA!C[41]:C[43],3,0))),0,GETV())"
Range("D12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-11]C[-3],DATA!C[46]:C[48],3,0))),0,GETV())"
Range("D13").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-12]C[-3],DATA!C[36]:C[38],3,0))),0,GETV())"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-5]C[-4],DATA!C[10]:C[13],4,0))),0,GETV())"
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-6]C[-4],DATA!C[15]:C[18],4,0))),0,GETV())"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-7]C[-4],DATA!C[20]:C[23],4,0))),0,GETV())"
Range("E9").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-8]C[-4],DATA!C[25]:C[28],4,0))),0,GETV())"
Range("E10").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-9]C[-4],DATA!C[30]:C[33],4,0))),0,GETV())"
Range("E11").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-10]C[-4],DATA!C[40]:C[43],4,0))),0,GETV())"
Range("E12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-11]C[-4],DATA!C[45]:C[48],4,0))),0,GETV())"
Range("E13").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-12]C[-4],DATA!C[35]:C[38],4,0))),0,GETV())"
Range("E14").Select
Range("A19").Select
ActiveCell.FormulaR1C1 = "CAN"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A20").Select
ActiveCell.FormulaR1C1 = "CAP"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A21").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A21").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("A21").Select
ActiveCell.FormulaR1C1 = "CAR"
Range("A22").Select
ActiveCell.FormulaR1C1 = "CAS"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A23").Select
ActiveCell.FormulaR1C1 = "CAU"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A24").Select
ActiveCell.FormulaR1C1 = "CAX"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A24").Select
ActiveCell.FormulaR1C1 = "CAW"
Range("A25").Select
ActiveCell.FormulaR1C1 = "CAX"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A26").Select
ActiveCell.FormulaR1C1 = "CAY"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C19").Select
ActiveCell.FormulaR1C1 = "C10"
Range("C20").Select
ActiveCell.FormulaR1C1 = "C12"
Range("C21").Select
ActiveCell.FormulaR1C1 = "C20"
Range("C22").Select
ActiveCell.FormulaR1C1 = "C25"
Range("C23").Select
ActiveCell.FormulaR1C1 = "C35"
Range("C24").Select
ActiveCell.FormulaR1C1 = "C37"
Range("C25").Select
ActiveCell.FormulaR1C1 = "C40"
Range("C26").Select
ActiveCell.FormulaR1C1 = "C45"
Range("C27").Select
ActiveCell.FormulaR1C1 = "C50"
Range("C28").Select
ActiveCell.FormulaR1C1 = "C55"
Range("C29").Select
ActiveCell.FormulaR1C1 = "C60"
Range("C30").Select
ActiveCell.FormulaR1C1 = "C80"
Range("C31").Select
ActiveCell.FormulaR1C1 = "C90"
Range("C32").Select
ActiveCell.FormulaR1C1 = "C96"
Range("A19:A26").Select
Selection.Font.Bold = False
Range("E19").Select
ActiveCell.FormulaR1C1 = "G40"
Range("E20").Select
ActiveCell.FormulaR1C1 = "G45"
Range("E21").Select
ActiveCell.FormulaR1C1 = "G50"
Range("G19").Select
ActiveCell.FormulaR1C1 = "PM1"
Range("G20").Select
ActiveCell.FormulaR1C1 = "PM2"
Range("G21").Select
ActiveCell.FormulaR1C1 = "PMA"
Range("G22").Select
ActiveCell.FormulaR1C1 = "PMD"
Range("G23").Select
ActiveCell.FormulaR1C1 = "PML"
Range("G24").Select
ActiveCell.FormulaR1C1 = "PMX"
Range("G25").Select
ActiveCell.FormulaR1C1 = "PMZ"
Range("I19").Select
ActiveCell.FormulaR1C1 = "S10"
Range("I20").Select
ActiveCell.FormulaR1C1 = "S20"
Range("I21").Select
ActiveCell.FormulaR1C1 = "S30"
Range("I22").Select
ActiveCell.FormulaR1C1 = "S50"
Range("I23").Select
ActiveCell.FormulaR1C1 = "S80"
Range("I24").Select
ActiveCell.FormulaR1C1 = "S85"
Range("I25").Select
ActiveCell.FormulaR1C1 = "S96"
Range("I26").Select
ActiveCell.FormulaR1C1 = "S97"
Range("I27").Select
ActiveCell.FormulaR1C1 = "S98"
Range("K19").Select
ActiveCell.FormulaR1C1 = "W01"
Range("K20").Select
ActiveCell.FormulaR1C1 = "W50"
Range("K21").Select
ActiveCell.FormulaR1C1 = "W90"
Range("K22").Select
ActiveCell.FormulaR1C1 = "W92"
Range("K23").Select
ActiveCell.FormulaR1C1 = "W96"
Range("M19").Select
ActiveCell.FormulaR1C1 = "YG1"
Range("M20").Select
ActiveCell.FormulaR1C1 = "YG2"
Range("M21").Select
ActiveCell.FormulaR1C1 = "YG3"
Range("M22").Select
ActiveCell.FormulaR1C1 = "YG4"
Range("M23").Select
ActiveCell.FormulaR1C1 = "YG5"
Range("M24").Select
ActiveCell.FormulaR1C1 = "YG6"
Range("M25").Select
ActiveCell.FormulaR1C1 = "YG7"
Range("M26").Select
ActiveCell.FormulaR1C1 = "YG8"
Range("M27").Select
ActiveCell.FormulaR1C1 = "YGA"
Range("M28").Select
ActiveCell.FormulaR1C1 = "YGB"
Range("M29").Select
ActiveCell.FormulaR1C1 = "YGD"
Range("M30").Select
Range("B19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B19:B26").Select
Selection.FillDown
Range("B20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B19").Select
Selection.Copy
Range("D19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("J19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("L19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("N19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D19").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D19:D32").Select
Selection.FillDown
Range("D20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D27").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-26]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D28").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-27]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D29").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-28]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D30").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-29]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D31").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-30]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D32").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-31]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("F19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-5]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("F19:F21").Select
Selection.FillDown
Range("F20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-5]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("F21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-5]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H19:H25").Select
Selection.FillDown
Range("H20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J19:J27").Select
Selection.FillDown
Range("J20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J27").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-26]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L19:L23").Select
Selection.FillDown
Range("L20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N19:N29").Select
Selection.FillDown
Range("N20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N27").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-26]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N28").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-27]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N29").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-28]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("A1").Select
ActiveCell.FormulaR1C1 = " "
Range("A1").Select
End Sub
Sub Sales_Analysis()
ChDir "Z:\jeffw"
Workbooks.OpenText FileName:="Z:\jeffw\CUST.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(11 _
, 2), Array(43, 2))
Workbooks.Add
Windows("CUST.TXT").Activate
Columns("A:C").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("K1").Select
Windows("CUST.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\CONTRACT.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(13, 2))
Columns("A:C").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("O1").Select
Windows("CONTRACT.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\ALL.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=6
Range("O4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ALL"
Range("T1").Select
Windows("ALL.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\CARBOLOY.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("T4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("Y1").Select
Windows("CARBOLOY.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\CLEVELAND.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Book2").Activate
ActiveSheet.Paste
Range("Y4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CLEVELAND"
Range("AD1").Select
Windows("CLEVELAND.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\GREENFIELD.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AD4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "GREENFIELD"
Range("AI1").Select
Windows("GREENFIELD.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\MARSHALL.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AI4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "MARSHALL"
Range("AN1").Select
Windows("MARSHALL.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\SGS.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Book2").Activate
ActiveSheet.Paste
Range("AN4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "SGS"
Range("AS1").Select
Windows("SGS.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\WELDON.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AS4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "WELDON"
Range("AX1").Select
Windows("WELDON.TXT").Activate
ActiveWindow.Close
Workbooks.OpenText FileName:="Z:\jeffw\YG1.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(10 _
, 2), Array(15, 9), Array(56, 2), Array(68, 2), Array(81, 2), Array(94, 9))
Columns("A:D").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Range("AX4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "YG1"
Range("A1").Select
Windows("YG1.TXT").Activate
ActiveWindow.Close
ChDir "C:\Documents and Settings\jeff ward\My Documents\SALES ANALYSIS"
Columns("A:A").Select
Selection.NumberFormat = "@"
Range("A1").Select
ActiveWorkbook.Save
Range("N1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]&""@""&RC[-2]"
Range("N2").Select
Columns("N:N").ColumnWidth = 14.14
Range("N1:N10000").Select
Selection.FillDown
ActiveWindow.ScrollRow = 1
Columns("K:N").Select
Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Key2:=Range("N1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("J1").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "SALES-ANALYSIS"
Sheets("DATA").Select
Sheets("DATA").Move After:=Sheets(2)
Sheets("SALES-ANALYSIS").Select
Columns("B:B").Select
Selection.ColumnWidth = 12.43
Columns("C:D").Select
Selection.ColumnWidth = 10.14
Range("B2").Select
ActiveCell.FormulaR1C1 = "SLSP"
Range("C5").Select
ActiveCell.FormulaR1C1 = "24 mo total"
Range("D5").Select
ActiveCell.FormulaR1C1 = "12 mo total"
Range("E5").Select
ActiveCell.FormulaR1C1 = "6 mo total"
Range("B6").Select
ActiveCell.FormulaR1C1 = "TOTAL"
Range("B7").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("B8").Select
ActiveCell.FormulaR1C1 = "CLEVELAND"
Range("B9").Select
ActiveCell.FormulaR1C1 = "GREENFIELD"
Range("B10").Select
ActiveCell.FormulaR1C1 = "MARSHALL"
Range("B11").Select
ActiveCell.FormulaR1C1 = "WELDON"
Range("B12").Select
ActiveCell.FormulaR1C1 = "YG1"
Range("B13").Select
ActiveCell.FormulaR1C1 = "SGS"
Range("A1:B1").Select
Selection.Font.Bold = True
Range("A16").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "CURRENT CONTRACTS"
Rows("17:18").Select
Selection.Font.Bold = True
Range("A17").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("A18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("B18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("C17").Select
ActiveCell.FormulaR1C1 = "CLEVELAND"
Range("C18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("D18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("E17").Select
ActiveCell.FormulaR1C1 = "GREENFIELD"
Range("E18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("F18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("G17").Select
ActiveCell.FormulaR1C1 = "MARSHALL"
Range("G18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("H18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("I17").Select
ActiveCell.FormulaR1C1 = "SGS"
Range("I18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("J18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("K17").Select
ActiveCell.FormulaR1C1 = "WELDON"
Range("K18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("L18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("M17").Select
ActiveCell.FormulaR1C1 = "YG1"
Range("M18").Select
ActiveCell.FormulaR1C1 = "CLS"
Range("N18").Select
ActiveCell.FormulaR1C1 = "MULT"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],DATA!C[-1]:C,2,0)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C[-2],DATA!C[-2]:C,3,0)"
Range("C3").Select
Range("A1").Select
Selection.NumberFormat = "@"
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-5]C[-2],DATA!C[12]:C[13],2,0))),0,GETV())"
Range("C7").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-6]C[-2],DATA!C[17]:C[18],2,0))),0,GETV())"
Range("C8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-7]C[-2],DATA!C[22]:C[23],2,0))),0,GETV())"
Range("C9").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-8]C[-2],DATA!C[27]:C[28],2,0))),0,GETV())"
Range("C10").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-9]C[-2],DATA!C[32]:C[33],2,0))),0,GETV())"
Range("C11").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-10]C[-2],DATA!C[42]:C[43],2,0))),0,GETV())"
Range("C12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-11]C[-2],DATA!C[47]:C[48],2,0))),0,GETV())"
Range("C13").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-12]C[-2],DATA!C[37]:C[38],2,0))),0,GETV())"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-5]C[-3],DATA!C[11]:C[13],3,0))),0,GETV())"
Range("D7").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-6]C[-3],DATA!C[16]:C[18],3,0))),0,GETV())"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-7]C[-3],DATA!C[21]:C[23],3,0))),0,GETV())"
Range("D9").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-8]C[-3],DATA!C[26]:C[28],3,0))),0,GETV())"
Range("D10").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-9]C[-3],DATA!C[31]:C[33],3,0))),0,GETV())"
Range("D11").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-10]C[-3],DATA!C[41]:C[43],3,0))),0,GETV())"
Range("D12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-11]C[-3],DATA!C[46]:C[48],3,0))),0,GETV())"
Range("D13").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-12]C[-3],DATA!C[36]:C[38],3,0))),0,GETV())"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-5]C[-4],DATA!C[10]:C[13],4,0))),0,GETV())"
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-6]C[-4],DATA!C[15]:C[18],4,0))),0,GETV())"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-7]C[-4],DATA!C[20]:C[23],4,0))),0,GETV())"
Range("E9").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-8]C[-4],DATA!C[25]:C[28],4,0))),0,GETV())"
Range("E10").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-9]C[-4],DATA!C[30]:C[33],4,0))),0,GETV())"
Range("E11").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-10]C[-4],DATA!C[40]:C[43],4,0))),0,GETV())"
Range("E12").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-11]C[-4],DATA!C[45]:C[48],4,0))),0,GETV())"
Range("E13").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(VLOOKUP(R[-12]C[-4],DATA!C[35]:C[38],4,0))),0,GETV())"
Range("E14").Select
Range("A19").Select
ActiveCell.FormulaR1C1 = "CAN"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A20").Select
ActiveCell.FormulaR1C1 = "CAP"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A21").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A21").Select
ActiveCell.FormulaR1C1 = "CARBOLOY"
Range("A21").Select
ActiveCell.FormulaR1C1 = "CAR"
Range("A22").Select
ActiveCell.FormulaR1C1 = "CAS"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A23").Select
ActiveCell.FormulaR1C1 = "CAU"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A24").Select
ActiveCell.FormulaR1C1 = "CAX"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A24").Select
ActiveCell.FormulaR1C1 = "CAW"
Range("A25").Select
ActiveCell.FormulaR1C1 = "CAX"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A26").Select
ActiveCell.FormulaR1C1 = "CAY"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C19").Select
ActiveCell.FormulaR1C1 = "C10"
Range("C20").Select
ActiveCell.FormulaR1C1 = "C12"
Range("C21").Select
ActiveCell.FormulaR1C1 = "C20"
Range("C22").Select
ActiveCell.FormulaR1C1 = "C25"
Range("C23").Select
ActiveCell.FormulaR1C1 = "C35"
Range("C24").Select
ActiveCell.FormulaR1C1 = "C37"
Range("C25").Select
ActiveCell.FormulaR1C1 = "C40"
Range("C26").Select
ActiveCell.FormulaR1C1 = "C45"
Range("C27").Select
ActiveCell.FormulaR1C1 = "C50"
Range("C28").Select
ActiveCell.FormulaR1C1 = "C55"
Range("C29").Select
ActiveCell.FormulaR1C1 = "C60"
Range("C30").Select
ActiveCell.FormulaR1C1 = "C80"
Range("C31").Select
ActiveCell.FormulaR1C1 = "C90"
Range("C32").Select
ActiveCell.FormulaR1C1 = "C96"
Range("A19:A26").Select
Selection.Font.Bold = False
Range("E19").Select
ActiveCell.FormulaR1C1 = "G40"
Range("E20").Select
ActiveCell.FormulaR1C1 = "G45"
Range("E21").Select
ActiveCell.FormulaR1C1 = "G50"
Range("G19").Select
ActiveCell.FormulaR1C1 = "PM1"
Range("G20").Select
ActiveCell.FormulaR1C1 = "PM2"
Range("G21").Select
ActiveCell.FormulaR1C1 = "PMA"
Range("G22").Select
ActiveCell.FormulaR1C1 = "PMD"
Range("G23").Select
ActiveCell.FormulaR1C1 = "PML"
Range("G24").Select
ActiveCell.FormulaR1C1 = "PMX"
Range("G25").Select
ActiveCell.FormulaR1C1 = "PMZ"
Range("I19").Select
ActiveCell.FormulaR1C1 = "S10"
Range("I20").Select
ActiveCell.FormulaR1C1 = "S20"
Range("I21").Select
ActiveCell.FormulaR1C1 = "S30"
Range("I22").Select
ActiveCell.FormulaR1C1 = "S50"
Range("I23").Select
ActiveCell.FormulaR1C1 = "S80"
Range("I24").Select
ActiveCell.FormulaR1C1 = "S85"
Range("I25").Select
ActiveCell.FormulaR1C1 = "S96"
Range("I26").Select
ActiveCell.FormulaR1C1 = "S97"
Range("I27").Select
ActiveCell.FormulaR1C1 = "S98"
Range("K19").Select
ActiveCell.FormulaR1C1 = "W01"
Range("K20").Select
ActiveCell.FormulaR1C1 = "W50"
Range("K21").Select
ActiveCell.FormulaR1C1 = "W90"
Range("K22").Select
ActiveCell.FormulaR1C1 = "W92"
Range("K23").Select
ActiveCell.FormulaR1C1 = "W96"
Range("M19").Select
ActiveCell.FormulaR1C1 = "YG1"
Range("M20").Select
ActiveCell.FormulaR1C1 = "YG2"
Range("M21").Select
ActiveCell.FormulaR1C1 = "YG3"
Range("M22").Select
ActiveCell.FormulaR1C1 = "YG4"
Range("M23").Select
ActiveCell.FormulaR1C1 = "YG5"
Range("M24").Select
ActiveCell.FormulaR1C1 = "YG6"
Range("M25").Select
ActiveCell.FormulaR1C1 = "YG7"
Range("M26").Select
ActiveCell.FormulaR1C1 = "YG8"
Range("M27").Select
ActiveCell.FormulaR1C1 = "YGA"
Range("M28").Select
ActiveCell.FormulaR1C1 = "YGB"
Range("M29").Select
ActiveCell.FormulaR1C1 = "YGD"
Range("M30").Select
Range("B19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B19:B26").Select
Selection.FillDown
Range("B20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-1]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("B19").Select
Selection.Copy
Range("D19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("J19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("L19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("N19").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D19").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D19:D32").Select
Selection.FillDown
Range("D20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D27").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-26]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D28").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-27]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D29").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-28]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D30").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-29]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D31").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-30]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("D32").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-31]C[-3]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("F19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-5]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("F19:F21").Select
Selection.FillDown
Range("F20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-5]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("F21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-5]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H19:H25").Select
Selection.FillDown
Range("H20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("H25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-7]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J19:J27").Select
Selection.FillDown
Range("J20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("J27").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-26]C[-9]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L19:L23").Select
Selection.FillDown
Range("L20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("L23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-11]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-18]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N19:N29").Select
Selection.FillDown
Range("N20").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-19]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N21").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-20]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N22").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-21]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N23").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-22]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N24").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-23]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N25").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-24]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N26").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-25]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N27").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-26]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N28").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-27]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("N29").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(SETV(INDEX(DATA!R1C13:R10000C13,MATCH(R[-28]C[-13]&""@""&RC[-1],DATA!R1C14:R10000C14,0)))),0,GETV())"
Range("A1").Select
ActiveCell.FormulaR1C1 = " "
Range("A1").Select
End Sub