Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank Data using VBA - having trouble with R1C1 notation
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 :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank Data using VBA - having trouble with R1C1 notation
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank Data using VBA - having trouble with R1C1 notation
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank Data using VBA - having trouble with R1C1 notation
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank Data using VBA - having trouble with R1C1 notation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
R1C1 Notation??? | Excel Programming | |||
R1C1 notation in VBA code | Excel Programming | |||
Sumproduct with A1 notation instead of R1C1 | Excel Programming | |||
R1C1 Notation in VBA | Excel Programming | |||
R1C1 Notation | Excel Programming |