cleaning up a recorded macro?

J

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
947
Joe4
Joe4
P
  • Question
Help with Vba Code
Replies
3
Views
154
Coyotex3
C
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
314
kevin9999
K
D
  • Question
VBA optimization advice
Replies
3
Views
92
bobsan42
bobsan42
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
204
Coco1
C

Forum statistics

Threads
1,214,784
Messages
6,121,538
Members
449,038
Latest member
Guest1337

Share this page

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
Back
Top