Declaring string in R1C1 with Rank function?

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)"

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

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.

Well, the columns will be the same on any particular run of the macro, but as my sets of data change the columns will change.

However, I'm starting to think now I could declare variables and set those equal to the range I'm working with.
I already have a variable set to the upper-left of my range of data to be ranked. So now I could probably just do something like this:

Please Login or Register  to view this content.

I'll try this out and see how it goes. Thanks for the assistance.