PDA

View Full Version : help altering recorded code please



mercmannick
04-26-2007, 11:23 AM
Windows("zf 15.3").Activate
Range("F1").Select
Selection.EntireRow.Delete
Range("F4").Select
Selection.EntireRow.Delete
Columns("B:M").Select
Columns("B:M").EntireColumn.AutoFit
Range("C3").Select
Selection.AutoFilter
Columns("B:B").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("C3").Select
Selection.AutoFilter Field:=2, Criteria1:="=t*", Operator:=xlOr, _
Criteria2:="=l*"
Range("C374:C3237").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=2, Criteria1:="=m*", Operator:=xlOr, _
Criteria2:="=l*"
Selection.EntireRow.Delete
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=5, Criteria1:="="
Range("C4:C3198").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=5, Criteria1:="planned"
Range("C167:C1741").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=3, Criteria1:="=b0*", Operator:=xlAnd
Range("D4").Select
ActiveCell.FormulaR1C1 = "S70"
Range("D4").Select
Selection.FillDown
Range("D3").Select
Selection.AutoFilter Field:=3, Criteria1:="=B15", Operator:=xlAnd
Range("D494").Select
ActiveCell.FormulaR1C1 = "S17"
Selection.FillDown
Range("D3").Select
Selection.AutoFilter Field:=3, Criteria1:="S12"
Range("D1602").Select
ActiveCell.FormulaR1C1 = "S40"
Range("D1602").Select
Selection.FillDown
Range("D3").Select
Selection.AutoFilter Field:=3, Criteria1:="=i0*", Operator:=xlAnd
Range("D565").Select
ActiveCell.FormulaR1C1 = "S03C"
Selection.FillDown
Range("D3").Select
Selection.AutoFilter Field:=3, Criteria1:="=i4*", Operator:=xlAnd
Range("D580").Select
ActiveCell.FormulaR1C1 = "S03E"
Selection.FillDown
Range("D3").Select
Selection.AutoFilter Field:=3, Criteria1:="=i5*", Operator:=xlAnd
Range("D844").Select
ActiveCell.FormulaR1C1 = "S03F"
Selection.FillDown
Range("D3").Select
Selection.AutoFilter Field:=3, Criteria1:="I60"
Range("D1314").Select
ActiveCell.FormulaR1C1 = "S03W"
Selection.FillDown
Range("D3").Select
Selection.AutoFilter Field:=3, Criteria1:="=i*", Operator:=xlAnd
Range("D1381").Select
ActiveCell.FormulaR1C1 = "S03G"
Selection.FillDown
Range("D3").Select
ActiveWindow.SmallScroll Down:=-15
Selection.AutoFilter Field:=3
Range("B4:M1714").Select

i am trying to read this code and change it to normal VBA

any help would be appreciated

Merc :banghead::banghead::banghead:

Bob Phillips
04-26-2007, 11:45 AM
No guarantees, haven;t even attempted to test it



Windows("zf 15.3").Activate
Range("F1").EntireRow.Delete
Range("F4").EntireRow.Delete
Columns("B:M").EntireColumn.AutoFit
Range("C3").AutoFilter
Columns("B:B").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("C3").AutoFilter Field:=2, Criteria1:="=t*", Operator:=xlOr, _
Criteria2:="=l*"
With Range("C374:C3237")
.EntireRow.Delete
.AutoFilter Field:=2, Criteria1:="=m*", Operator:=xlOr, _
Criteria2:="=l*"
.EntireRow.Delete
.AutoFilter Field:=2
.AutoFilter Field:=5, Criteria1:="="
End With
With Range("C4:C3198")
.EntireRow.Delete
.AutoFilter Field:=5, Criteria1:="planned"
End With
With Range("C167:C1741")
.EntireRow.Delete
.AutoFilter Field:=5
.AutoFilter Field:=3, Criteria1:="=b0*", Operator:=xlAnd
End With
Range("D4").Value = "S70"
Range("D3").AutoFilter Field:=3, Criteria1:="=B15", Operator:=xlAnd
Range("D494").Value = "S17"
Range("D3").AutoFilter Field:=3, Criteria1:="S12"
Range("D1602").Value = "S40"
Range("D3").AutoFilter Field:=3, Criteria1:="=i0*", Operator:=xlAnd
Range("D565").Value = "S03C"
Range("D3").AutoFilter Field:=3, Criteria1:="=i4*", Operator:=xlAnd
Range("D580").Value = "S03E"
Range("D3").AutoFilter Field:=3, Criteria1:="=i5*", Operator:=xlAnd
Range("D844").Value = "S03F"
Range("D3").AutoFilter Field:=3, Criteria1:="I60"
Range("D1314").Value = "S03W"
Range("D3").AutoFilter Field:=3, Criteria1:="=i*", Operator:=xlAnd
Range("D1381").Value = "S03G"
Range("D3").AutoFilter Field:=3
Range("B4:M1714").Select

mercmannick
04-26-2007, 12:01 PM
xld thanks for reply

it is confusing me as this is what i have to do below...........

1.Open ZF**.*.xls file
2.Delete row between header and data
3.Columns B replace ?.? with ?/?
4.Set auto filter on
5.On short material col , auto filter custom begins with L , T or M delete all rows
6.Rep order column auto filter blanks and planned orders delete all rows
7.On outstanding ops column if 0 delete all
8.On mrp code column: auto filter B01-B05 change to S70, begins with I0 replace with S03C, begins with I4 replace with S03E, begins with I5 replace with S03F, begins with I6 replace with S03W all remaining begins with I7*-I99 replace with S03G, begins with B15 replace with S17
9.Copy all data below header row , and paste into everything page in IMF s starts
10.Current w centre col auto filter begins with 200 colour cells light blue, begins with S1 colour yellow, begins with S7 colour gold, begins with S0 colour red, begins with S4 colour light green
11.Highlight all data centre text , bold text and border round all text (internal and external)
12.Auto filter rec store col begins with S3 or 48D copy all , paste to internal page
13.Auto filter sched start column date out to next Monday colour black and font white
14.Internal page data sort by cell ascending , rep order ascending , sched start descending save book
15.Everything page data sort rep order , remove duplicate entry?s
16.Auto filter rec store begins with 05, copy all to external page, data sort by sched start week today black cell and font white
17.external page data sort by cell ascending , rep order ascending , sched start descending save book



i have the 2 sample books if that will help



Merc