Hello, so I'm working on setting up a macro that ranks a bunch of data but the range the rank function and the data is in is dynamic so the parameters of the ranking is controlled by variables.

Now, the line below works for creating this formula in the cell: "=RANK(L23, L23:T23, 1)"



ActiveCell.FormulaR1C1 = "=Rank(RC[" & -n - 1 & "], RC[" & -n - 1 & "]:RC[-2], 1)"
The problem is that my method is to create a formula for the cell in the upper-left of the ranking range, and then copy/paste it across the rest of the range. That means I *need* the formula in the cell to be....


"=RANK(L23, $L23:$T23, 1)"

Now...I was hoping I could just go and say...

ActiveCell.FormulaR1C1 = "=Rank(RC[" & -n - 1 & "], $RC[" & -n - 1 & "]:$RC[-2], 1)"
But this generates an "application-defined or object-defined error" --

How do I fix this? If you can think of a way besides using R1C1 that's fine too, doesn't much matter how it happens I just need it to happen.

Thanks, if you have any questions or would like to see anything let me know.