cleaning up a recorded macro?

JWARD

Board Regular
Joined
Aug 6, 2004
Messages
137
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
owkay.. I'm not sure of what you are doing here, so I don't know what you can delete, and what you should store.
I suggest at least splitting it into several parts, for example the opening and copying of "cust.txt" you can put in a different macro.

Does this macro do what you want?
If yes, why should you clean it up.
If no, what does it do you do not want?
I think there is a much shorter way to achieve your goal here.

I feel like you should post what you are trying to achieve.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Yeah, that's one pug-ugly piece o' code. :eek:

Best to describe, in steps, what you want to do and we'll put our heads together.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
WOW what a large list

let's see

avoid to select
Columns("A:C").Select
Selection.Copy
Columns("A:C").Copy

Windows("Book2").Activate
ActiveSheet.Paste
Range("O1").Select
Windows("CONTRACT.TXT").Activate
it 's clearer to select (when you really need select!) just before the operation you will do, in this part you "go away" straight after the selection

unnecessary Lines
ActiveWindow.SmallScroll ToRight:=6
Application.CutCopyMode = False (unless at the end to take away the "running ants")

Range("AI4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "MARSHALL"
change to
Range("AI4") = "MARSHALL"

I hope you will understand the logic
start cleaning up your macro this way

afterwards post back and we'll see what else can be done to make it
1. readable
2. working without screenflickering
3. faster

kind regards,
Erik
 

Forum statistics

Threads
1,147,846
Messages
5,743,515
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top