Rank Data using VBA - having trouble with R1C1 notation

[Harry Flashman]

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

 

[Harry Flashman]

I have just realized that my macro does not work if there is more than
two columns (where only one of the columns actually has the data I
wished to rank).

I am just learning VBA.
I started out with a another version of this macro, which used named
ranges rather than the Set funtion (or is it a method).
This macro works for up to 26 columns, but it clumsy because it
creates a bunch of named ranges.
I have pasted this so people can see how the macro is supposed to
work. I am pretty sure I can fix the other one so it works like this
one, but I still need to figure out R1C1 notation.

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

 

[Harry Flashman]

Actually the above macro is not quite right. This one works

Sub RankData2()
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(0,
1).End(xlDown))
MyData.Offset(0, c + e).Formula = "=RANK(" & Z & u & "," & Z & "$" & u
& ":" & Z & "$" & t & ",0)"
End Sub

But I sill need to figure out R1C1 notation (with variables).

 

[Harry Flashman]

Okay I have figured out R1C1 notation.
Here is my solution. It's a bit convoluted but it seems to work:
Guess it was silly of me to post here, but oh well.

Sub RankData3()
Set myRange = Selection
e = InputBox("How many columns should the gap be?")
y = myRange.Column
Z = Chr(y + 65)
c = myRange.Columns.Count
q = myRange.Column + c - 1
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(0,
1).End(xlDown))
f = myData.Row
myData.Offset(0, c + e).FormulaR1C1 = "=RANK(RC[" & (-1 * (c + e)) &
"],R" & f & "C" & q & ":R" & t & "C" & q & ",0)"

End Sub

 

[Harry Flashman]

I think I figured out R1C1. This looks a bit clumsy but it seems to
work.
Now I need to learn how to select the location for the ranked data.
It's probably silly posting this since I am the only one in this
thread, but nevermind.

Sub RankData3()
Set myRange = Selection
e = InputBox("How many columns should the gap be?")
c = myRange.Columns.Count
q = myRange.Column + c - 1
t = myRange.Rows.Count + myRange.Row - 1
myRange.Copy myRange.Offset(0, c + e)
Set myData = Range(ActiveCell.Offset(1, c - 1), ActiveCell.Offset(0,
1).End(xlDown))
f = myData.Row
g = (-1 * (c + e))
myData.Offset(0, c + e).FormulaR1C1 = "=RANK(RC[" & g & "],R" & f &
"C[" & g & "]:R" & t & "C[" & g & "],0)"
End Sub