Cant make this small thing work...can some1 pls check.. [using formula in vba]

[pedie]

It errors...using formula in vba
<TABLE style="WIDTH: 50pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=67><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #953735; WIDTH: 50pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 width=67>#NAME?</TD></TR></TBODY></TABLE>
and formula displays this way...=RANK('i1','i1':'I22')

actual formula= "=RANK(I1,$I$1:$I$22)"

[FONT=Courier New]Dim lr as long[/FONT]
[FONT=Courier New]lr = Sheet4.Range("H" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]Sheet4.Activate[/FONT]
[FONT=Courier New]   Range("J1:J" & lr).FormulaR1C1 = "=RANK(I1,$I$1:$I$" & lr & ")"[/FONT]

 

[pedie]

Sorry it was suppose to be this way i think...

[/FONT]
[FONT=Courier New]Range("J1:J" & lr).Formula = "=RANK(I1,$I$1:$I$" & lr & ")"

 

[jasonb75]

In R1C1

Range("J1:J" & lr).FormulaR1C1 = "=RANK(RC9,R1C9:R" & lr & "C9)"

 

[pedie]

In R1C1

Range("J1:J" & lr).FormulaR1C1 = "=RANK(RC9,R1C9:R" & lr & "C9)"

Oh Jason...i really appriciate you helping me now and then...

Thanks alot!

Good Night!!!