looping formula - r1c1 problem

  • Thread starter Thread starter MDC
  • Start date Start date
M

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)
 
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.
 
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)
Click to expand...


.
Click to expand...
 
Back
Top