I need to loop a ranking formula. The problem I am having
is with the R[1]C[1] method.

Here's what I have.
lngRow = 6

For lngRow2 = lngSecondRow To lngLastRow2 Step 1
If Len(RTrim$(.Cells(lngRow2, 2))) 0 Then
Cells(lngRow2, 1).FormulaR1C1 = _
'PROBLEM IS HERE!!!
"=RANK(R[" & lngRow & "]C[9],myrank)"
lngRow = lngRow + 1
End If
Next lngRow2

It will work if the formula we
"=RANK(R[-67]C[9],myrank)" , but, it won't always be that
many rows.

Please help

Hi:

I'm not sure what you mean by "PROBLEM IS HERE". I'm not having any problem
with the following:

Sub RankFormula()
Dim lngRow As Integer
lngRow = -12
ActiveCell.FormulaR1C1 = "=RANK(R[" & lngRow & "]C[],myrank)"
End Sub

Regards,

Vasant.


"MDC" wrote in message
...
I need to loop a ranking formula. The problem I am having
is with the R[1]C[1] method.

Here's what I have.
lngRow = 6

For lngRow2 = lngSecondRow To lngLastRow2 Step 1
If Len(RTrim$(.Cells(lngRow2, 2))) 0 Then
Cells(lngRow2, 1).FormulaR1C1 = _
'PROBLEM IS HERE!!!
"=RANK(R[" & lngRow & "]C[9],myrank)"
lngRow = lngRow + 1
End If
Next lngRow2

It will work if the formula we
"=RANK(R[-67]C[9],myrank)" , but, it won't always be that
many rows.

Please help

I can't use "lngRow = -12" because the number of rows may
vary. The lngRow will always start in row 6.

-----Original Message-----
Hi:

I'm not sure what you mean by "PROBLEM IS HERE". I'm not
having any problem
with the following:

Sub RankFormula()
Dim lngRow As Integer
lngRow = -12
ActiveCell.FormulaR1C1 = "=RANK(R[" & lngRow & "]C
[],myrank)"
End Sub

Regards,

Vasant.


"MDC" wrote in
message
...
I need to loop a ranking formula. The problem I am
having
is with the R[1]C[1] method.

Here's what I have.
lngRow = 6

For lngRow2 = lngSecondRow To lngLastRow2 Step 1
If Len(RTrim$(.Cells(lngRow2, 2))) 0 Then
Cells(lngRow2, 1).FormulaR1C1 = _
'PROBLEM IS HERE!!!
"=RANK(R[" & lngRow & "]C[9],myrank)"
lngRow = lngRow + 1
End If
Next lngRow2

It will work if the formula we
"=RANK(R[-67]C[9],myrank)" , but, it won't always be
that
many rows.

Please help



.