Solved: Dynamic range reference

[Action925]

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.

[Bob Phillips]

Sub Ranking()
Dim iLastrow As Long
iLastrow = Cells(Rows.Count, "H").End(xlUp).Row
Range("H2").FormulaR1C1 = "Held Risk Filter"
Range("H3").FormulaR1C1 = "=IF(RC[-7]=""Held"",RC[-3],0)"
Range("H3").AutoFill Destination:=Range("H3:H" & iLastrow)
Range("H2:H" & iLastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"I2"), Unique:=True
Range("I3").Delete Shift:=xlUp
Range("J2").FormulaR1C1 = "Quintile"
Range("J3").FormulaR1C1 = "1"
Range("J4").FormulaR1C1 = "2"
Range("J5").FormulaR1C1 = "3"
Range("J6").FormulaR1C1 = "4"
Range("J7").FormulaR1C1 = "5"
Range("K2").FormulaR1C1 = "Quintile Cutoff"
Range("K3").FormulaR1C1 = "=PERCENTILE(RC[-2]:R[499]C[-2],0.2)"
Range("K4").FormulaR1C1 = "=PERCENTILE(R[-1]C[-2]:R[498]C[-2],0.4)"
Range("K5").FormulaR1C1 = "=PERCENTILE(R[-2]C[-2]:R[497]C[-2],0.6)"
Range("K6").FormulaR1C1 = "=PERCENTILE(R[-3]C[-2]:R[496]C[-2],0.8)"
Range("K7").FormulaR1C1 = "=PERCENTILE(R[-4]C[-2]:R[495]C[-2],1)"
Range("H3").EntireColumn.Insert
With Range("H2")
.FormulaR1C1 = "Ranking"
With .Characters(Start:=1, Length:=7).Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 8
End With
End With
Range("H3").FormulaR1C1 = _
"=IF(RC[-3]<R3C12,1,IF(RC[-3]<R4C12,2,IF(RC[-3]<R5C12,3,IF(RC[-3]<R6C12,4,5))))"
.AutoFill Destination:=Range("H3:H" & iLastrow)
Range("A1").Select
End Sub

[Action925]

My god, becoming a member of this forum has already paid off... Wow.. Thanks xld!

[Action925]

Okay, so I've made some modifications, and it seems the code is now hung up on the part in red:

Sub RankingDynamic()
Dim iLastrow As Long
Dim jLastrow As Long
iLastrow = Cells(Rows.Count, "G").End(xlUp).Row
Range("H2").FormulaR1C1 = "Held Risk Filter"
Range("H3").FormulaR1C1 = "=IF(RC[-7]=""Held"",RC[-3],0)"
Range("H3").AutoFill Destination:=Range("H3:H" & iLastrow)
Range("H2:H" & iLastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"I2"), Unique:=True
jLastrow = Cells(Rows.Count, "I").End(x1Up).Row
Range("J2").FormulaR1C1 = "Held Risk Filter ex Zero"
Range("J3").FormulaR1C1 = "IF(RC[-1]=""0"","",RC[-1])"
Range("J3").AutoFill Destination:=Range("J3:J" & jLastrow)
Range("K2").FormulaR1C1 = "Quintile"
Range("K3").FormulaR1C1 = "1"
Range("K4").FormulaR1C1 = "2"
Range("K5").FormulaR1C1 = "3"
Range("K6").FormulaR1C1 = "4"
Range("K7").FormulaR1C1 = "5"
Range("L2").FormulaR1C1 = "Quintile Cutoff"
Range("L3").FormulaR1C1 = "=PERCENTILE(C10,0.2)"
Range("L4").FormulaR1C1 = "=PERCENTILE(C10,0.4)"
Range("L5").FormulaR1C1 = "=PERCENTILE(C10,0.6)"
Range("L6").FormulaR1C1 = "=PERCENTILE(C10,0.8)"
Range("L7").FormulaR1C1 = "=PERCENTILE(C10,1)"
Range("H3").EntireColumn.Insert
With Range("H2")
.FormulaR1C1 = "Ranking"
With .Characters(Start:=1, Length:=7).Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 8
End With
End With
Range("H3").FormulaR1C1 = _
"=IF(RC[-3]<R3C12,1,IF(RC[-3]<R4C12,2,IF(RC[-3]<R5C12,3,IF(RC[-3]<R6C12,4,5))))"
Range("H3").AutoFill Destination:=Range("H3:H" & iLastrow)
Range("A1").Select
End Sub

Any ideas? Thanks again! :banghead:

[Zack Barresse]

You have a 1 where you should have a lowercase L ...

jLastrow = Cells(Rows.Count, "I").End(x1Up).Row

.. should be ..

jLastrow = Cells(Rows.Count, "I").End(xlUp).Row

:)

[Action925]

Argggh! This brings back nightmares from the days of college comp sci courses.. I remember spending 14 hours just to discover a missing "s" somewhere in the thousands of lines of code... Ugh...

Thanks so much!

[Zack Barresse]

You're very welcome. :)

If this is solved, you can mark it as such by going to Thread Tools | Mark Solved | Perform Action.

[Action925]

Okay, apparently I'm really losing it here... For some, I had one of the lines as:

Range("J3").FormulaR1C1 = "IF(RC[-1]=""0"","",RC[-1])"

Now I know it should be this instead:

Range("J3").FormulaR1C1 = "=IF(RC[-1]=""0"","",RC[-1])"

However, for some reason, its hung on this line... Any ideas? Thank you so much again!

[Bob Phillips]

Okay, apparently I'm really losing it here... For some, I had one of the lines as:

Range("J3").FormulaR1C1 = "IF(RC[-1]=""0"","",RC[-1])"

Now I know it should be this instead:

Range("J3").FormulaR1C1 = "=IF(RC[-1]=""0"","",RC[-1])"

However, for some reason, its hung on this line... Any ideas? Thank you so much again!

It should be

Range("J3").FormulaR1C1 = "=IF(RC[-1]=""0"","""",RC[-1])"

or probably

Range("J3").FormulaR1C1 = "=IF(RC[-1]=0,"""",RC[-1])"

[Action925]

Thank you! I marked this thread solved!