VBA Rank Help

[Antony rx]

Hi,

My code is working to add some columns to a spreadsheet then put the rank number in each of the new columns.
My problem is that the data being ranked will vary in its row location.
I am at a loss of how to get rank to work on variables, can any please help me with what I'm doing wrong as I cant get the rank function to work on dynamic variables.

The commented lines in my code are my failed attempts at getting to work. I have it working for a static positing, but this isn't always correct.

Thank you for reading & hope you can help me !

Antony


'insert columns for batting
'start of row
sor = Row
'end of row
eor = Row + noofstores
Do While mycolumn < 24
Cells(1, mycolumn).Select
ActiveCell.EntireColumn.Insert
Cells(12, mycolumn) = "Batting"
For rowchk = sor To eor
'This code does work but the data isnt always in the same position, so doesnt always work
Cells(rowchk, mycolumn) = "=RANK(RC[-1],R22C[-1]:R40C[-1])"
'An alternative try that doesnt work
'Cells(rowchk, mycolumn) = Application.WorksheetFunction.Rank(Range(cells(rowchk,mycolumn), Range("A1:A6"))
'An alternative try that doesnt work
'Cells(rowchk, mycolumn) = Application.WorksheetFunction.Rank(Cells(rowchk, mycolumn), Cells(sor, mycolumn), Cells(eor, mycolumn))
'An alternative try that doesnt work
'Cells(rowchk, mycolumn) = "=RANK(RC[-1],"Cells(sor, mycolumn)"," Cells(eor, mycolumn)")"

Next rowchk
mycolumn = mycolumn + 2
Loop

 

[AlphaFrog]

This inserts a blank column in between each existing column. Then it puts a Rank formula in each cell in the inserted column to rank the values in the column to the left from Row 22 to the last used row in that column.

Sub Rank_Each_Column()

    Dim Lastcol As Long, Lastrow As Long, c As Long
    
    [COLOR="Green"]' Last used column on the sheet[/COLOR]
    Lastcol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

    ' insert columns for batting (from right to left)
    For c = Lastcol To 1 Step -1
        Columns(c + 1).Insert
        Lastrow = Cells(Rows.Count, c).End(xlUp).Row    [COLOR="Green"]' Last used row in the given column[/COLOR]
        Range(Cells(22, c + 1), Cells(Lastrow, c + 1)).FormulaR1C1 = "=RANK(RC[-1],R22C[-1]:R" & Lastrow & "C[-1])"
    Next c
        
End Sub

 

[Antony rx]

Hi,

Many thanks Alphafrog for your reply.

The adding of ranking columns works fine in my code. What I need help with is using the rank command when the rows to rank change.

For example it could be to rank rows from 22 to 42 or it could be 20 to 40.

Within my full code the macro identifies which row to start at, what I stuck with is how to get the rank command to work on a variable rather than as coded as present.

Can you help ?

Thanks

Antony

 

[AlphaFrog]

    'insert columns for batting
    
    'start of row
    sor = Row
    
    'end of row
    eor = Row + noofstores
    
    Do While mycolumn < 24
    
        Cells(1, mycolumn).EntireColumn.Insert
        Cells(12, mycolumn) = "Batting"
        
        [COLOR="Red"]Range(Cells(sor, mycolumn), Cells(eor, mycolumn)).FormulaR1C1  = "=RANK(RC[-1],R" & sor & "C[-1]:R" & eor & "C[-1])"[/COLOR]

        mycolumn = mycolumn + 2
        
    Loop

 

[Antony rx]

Excellent,

That did it

Thank you so much

Antony