looping formula - r1c1 problem

[MDC]

I need to loop a formula that references a cell.

A B
1 123 939
2 121 192
3 122 121
4 422 121
....
12 989 121
13 784 623

I need to place a formula in cell A20 (example) that is a
ranking of the items in range("A1:A13"). The problem I
run into is using the r1c1 formula, because the next time
I run the code, the number of rows may be different (i.e.
need to place the formula in cell A5000 and can't simply
use R[-19]C)

 

[Tom Ogilvy]

Will the formula always be 7 cells past the last entry in the ranked list

Range("A1").End(xldown).Offset(7,0).FormulaR1C1 = "=Sum(R1C:R[-7]C)"

This uses the sum as an example since you didn't say what type of ranking
you are doing.

 

[MDC]

The problem isn't how far down from the bottom of the
original list, the problem is how many rows back UP to row
6.

rank formula =RANK(J6,J6:J67)

SO...Cells(lngRow, 1).FormulaR1C1 = _
"=RANK(R[???]C[9],myrank)"

AND the problem is that the next time I need this the
range will change, e.g. J6:J78

-----Original Message-----
Will the formula always be 7 cells past the last entry in the ranked list

Range("A1").End(xldown).Offset(7,0).FormulaR1C1 = "=Sum (R1C:R[-7]C)"

This uses the sum as an example since you didn't say what type of ranking
you are doing.

--
Regards,
Tom Ogilvy

I need to loop a formula that references a cell.

A B
1 123 939
2 121 192
3 122 121
4 422 121
...
12 989 121
13 784 623

I need to place a formula in cell A20 (example) that is a
ranking of the items in range("A1:A13"). The problem I
run into is using the r1c1 formula, because the next time
I run the code, the number of rows may be different (i.e.
need to place the formula in cell A5000 and can't simply
use R[-19]C)

.