I have a table of numeric data.
This routine copies my original table to a new location, somewhere to
the right of the original table and then inserts a formula with the
RANK function. The routine I have figured out thus far works quite
well, but it does not use R1C1.
Let say we have a a thee column table. Column A list products, while
colums B and C list units sold by month.
We start with the table selected.

Sub RankData()
Set myRange = Selection
e = InputBox("How many columns should the gap be?")
y = myRange.Column
Z = Chr(y + 65)
c = myRange.Columns.Count
t = myRange.Rows.Count + myRange.Row - 1
u = myRange.Row + 1
myRange.Copy myRange.Offset(0, c + e)
Set myData = Range(ActiveCell.Offset(1, c - 1), ActiveCell.Offset(1, c
- 1).End(xlDown))
myData.Offset(0, c + e).Formula = "=RANK(" & Z & u & "," & Z & "$" & u
& ":" & Z & "$" & t & ",0)"
End Sub

The trouble with this routine is that it does not work if the original
table has more than 26 columns.
Can anyone suggest how I might modify it to use R1C1 notation instead?
The other modification I would like to make to alter the routine so
that I can specify any location in a workbook for the ranked data,
rather than somewhere to the right of the original data (that is on
the same rows but with the columns shifted to the right).
I would extremely grateful for any advice :)