looping Ranking formula - R[1]C[1]

[MDC]

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 were:
"=RANK(R[-67]C[9],myrank)" , but, it won't always be that
many rows.

Please help

 

[Vasant Nanavati]

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]

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.

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 were:
"=RANK(R[-67]C[9],myrank)" , but, it won't always be that
many rows.

Please help

.