Action925
11-15-2005, 12:59 PM
Hi everyone,
I have the following code that includes autofill and advance filtering (for unique entries which are then copied to a new column), but I can't seem to figure out how to pass a dynamic range reference so that the macro will work on files that do not have the same number of rows.
Sub Ranking()
'
' Ranking Macro
' Macro recorded 11/14/2005 by XXX
'
Range("H2").Select
ActiveCell.FormulaR1C1 = "Held Risk Filter"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-7]=""Held"",RC[-3],0)"
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H3001")
Range("H3:H3001").Select
Range("H2").Select
Range("H2:H3001").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"I2"), Unique:=True
Range("I3").Select
Selection.Delete Shift:=xlUp
Range("J2").Select
ActiveCell.FormulaR1C1 = "Quintile"
Range("J3").Select
ActiveCell.FormulaR1C1 = "1"
Range("J4").Select
ActiveCell.FormulaR1C1 = "2"
Range("J5").Select
ActiveCell.FormulaR1C1 = "3"
Range("J6").Select
ActiveCell.FormulaR1C1 = "4"
Range("J7").Select
ActiveCell.FormulaR1C1 = "5"
Range("K2").Select
ActiveCell.FormulaR1C1 = "Quintile Cutoff"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(RC[-2]:R[499]C[-2],0.2)"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-1]C[-2]:R[498]C[-2],0.4)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-2]C[-2]:R[497]C[-2],0.6)"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-3]C[-2]:R[496]C[-2],0.8)"
Range("K7").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-4]C[-2]:R[495]C[-2],1)"
Range("K8").Select
ActiveWindow.SmallScroll Down:=-15
Range("H3").Select
Selection.EntireColumn.Insert
Range("H2").Select
ActiveCell.FormulaR1C1 = "Ranking"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]<R3C12,1,IF(RC[-3]<R4C12,2,IF(RC[-3]<R5C12,3,IF(RC[-3]<R6C12,4,5))))"
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H3001")
Range("H3:H3001").Select
Range("A1").Select
End Sub
Can anyone help? I know there's OFFSET usage, but I couldn't get it to work with my code above.
I have the following code that includes autofill and advance filtering (for unique entries which are then copied to a new column), but I can't seem to figure out how to pass a dynamic range reference so that the macro will work on files that do not have the same number of rows.
Sub Ranking()
'
' Ranking Macro
' Macro recorded 11/14/2005 by XXX
'
Range("H2").Select
ActiveCell.FormulaR1C1 = "Held Risk Filter"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-7]=""Held"",RC[-3],0)"
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H3001")
Range("H3:H3001").Select
Range("H2").Select
Range("H2:H3001").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"I2"), Unique:=True
Range("I3").Select
Selection.Delete Shift:=xlUp
Range("J2").Select
ActiveCell.FormulaR1C1 = "Quintile"
Range("J3").Select
ActiveCell.FormulaR1C1 = "1"
Range("J4").Select
ActiveCell.FormulaR1C1 = "2"
Range("J5").Select
ActiveCell.FormulaR1C1 = "3"
Range("J6").Select
ActiveCell.FormulaR1C1 = "4"
Range("J7").Select
ActiveCell.FormulaR1C1 = "5"
Range("K2").Select
ActiveCell.FormulaR1C1 = "Quintile Cutoff"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(RC[-2]:R[499]C[-2],0.2)"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-1]C[-2]:R[498]C[-2],0.4)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-2]C[-2]:R[497]C[-2],0.6)"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-3]C[-2]:R[496]C[-2],0.8)"
Range("K7").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(R[-4]C[-2]:R[495]C[-2],1)"
Range("K8").Select
ActiveWindow.SmallScroll Down:=-15
Range("H3").Select
Selection.EntireColumn.Insert
Range("H2").Select
ActiveCell.FormulaR1C1 = "Ranking"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]<R3C12,1,IF(RC[-3]<R4C12,2,IF(RC[-3]<R5C12,3,IF(RC[-3]<R6C12,4,5))))"
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H3001")
Range("H3:H3001").Select
Range("A1").Select
End Sub
Can anyone help? I know there's OFFSET usage, but I couldn't get it to work with my code above.