R1C1 with variables and the Rank Function.

So need a code that will rank many different lines of data. This was my try at it but it's giving me a 400 error. I have no idea what it doesn't like about it. I'd really appreciate any help, if you have any questions let me know. Thanks for any attempts/effort.



Sub threemonthHoldings()

Dim r As Integer

Do
r = 0

Do
Range("BR13").Select
ActiveCell.FormulaR1C1 = "=rank(R[0]C[-21], R[0]C[-21 - r]:R[0]C[-9 - r], 0)"
r = r + 1
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))

ActiveCell.Offset(1, -12).Select

Loop Until IsEmpty(ActiveCell.Offset(0, -9))


End Sub

I'm not sure what formula references you want but you would use the VBA variable r within VBA and not within the actual formula. Maybe something like this.

ActiveCell.FormulaR1C1 = "=RANK(R[0]C[-21], R[]C[" & -21 + r & "]:R[]C[" & -9 + r & "], 0)"


Again, I don't know if this produces the range references you want, but it won't cause the 400 error.