none
How to pass entire column as Range in RANK function VBA Macro: Error: Unable to Get Rak Property of WorksheetFunction class RRS feed

  • Question

  • Hi 

    I have a dynamically rows added from userform data entry and based on score, the rank must be generated by vba code.  But I get the error unable to get rank property of worksheetfunction class 

    sub getRank()

        

        Dim lRow As Long
        Dim ws As Worksheet
        Dim ascore As Double
        Dim arank As Range
        Set arank= Range("D:D")
        Set ws = Worksheets("Paired")
        lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

        With ws

    .Cells(lRow, 1).Value = Me.textbox1.Value

     .Cells(lRow, 2).Value = Me.textbox2.Value

     .Cells(lRow, 3).Value = (.Cells(lRow, 1).Value + .Cells(lRow, 2).Value) 

     ascore = .Cells(lRow, 3).Value

     .Cells(lRow, 4).Value = IIf(ascore <> "", Application.WorksheetFunction.rank(ascore, arank, 1), "")

    End With

    End Sub


    NewBInVB



    Thursday, November 3, 2016 7:18 PM

Answers

  • Your error is not with rank, but with comparing ascore (a double) with the string "", using ascore <> "".

     .Cells(lRow, 4).Value = IIf(ascore <> "", Application.WorksheetFunction.rank(ascore, arank, 1), "")

    This would work:

     .Cells(lRow, 4).Value = IIf(ascore <> 0, Application.WorksheetFunction.rank(ascore, arank, 1), "")

    But your conditional seems more geared to ensuring that the textbox values aren't blank, so you should check that with code before using the Rank function.


    Thursday, November 3, 2016 7:51 PM
  • You are ranking the score against values that exist in column D - based on your code, column D is on the activesheet, but it is possible that is the same sheet as "Paired".  Also, you don't add that value to that column D, so the ranking will not update as more values are evaluated.  If you want the rank to update, use a column of formulas like  

    =IF(C2<>"",RANK(C2,SheetName!C:C,1),"")

    Copied down for as far as you need - as you add more values to column C of SheetName, then the formula will update automatically.

    • Marked as answer by NewBInCoding Thursday, November 3, 2016 9:54 PM
    Thursday, November 3, 2016 8:44 PM

All replies

  • Your error is not with rank, but with comparing ascore (a double) with the string "", using ascore <> "".

     .Cells(lRow, 4).Value = IIf(ascore <> "", Application.WorksheetFunction.rank(ascore, arank, 1), "")

    This would work:

     .Cells(lRow, 4).Value = IIf(ascore <> 0, Application.WorksheetFunction.rank(ascore, arank, 1), "")

    But your conditional seems more geared to ensuring that the textbox values aren't blank, so you should check that with code before using the Rank function.


    Thursday, November 3, 2016 7:51 PM
  • Thanks Bernie. I removed IFF condition and it worked. I will add it back to check if your solution works. 

    I have another issue with this code, since the sub function adds a new row with the details provided in userform textboxs the rank does not refresh if the application is closed and reopened. 

    Example: I enter 4 students scores from userform and it has created a new row in sheet with Rank 1 to 4 according to the score. This works fine if I continuously use the application but in case I run the code after closing the new student 5 is added but the Rank does not get refreshed or is in correct order and it restarts rank from 1 on closing & opening the application

    Score Rank
    108.55 1
    111.7 2
    109.95 2
    110.6 3
    110.65 4

     is the result instead of 

    Score           Rank
    108.55 1 1
    111.7 2 5
    109.95 2 2
    110.6 3 3
    110.65 4 4


    Thanks for the help


    NewBInVB


    Thursday, November 3, 2016 8:09 PM
  • You are ranking the score against values that exist in column D - based on your code, column D is on the activesheet, but it is possible that is the same sheet as "Paired".  Also, you don't add that value to that column D, so the ranking will not update as more values are evaluated.  If you want the rank to update, use a column of formulas like  

    =IF(C2<>"",RANK(C2,SheetName!C:C,1),"")

    Copied down for as far as you need - as you add more values to column C of SheetName, then the formula will update automatically.

    • Marked as answer by NewBInCoding Thursday, November 3, 2016 9:54 PM
    Thursday, November 3, 2016 8:44 PM
  • I did not opt to add formula directly on the sheet because there may be 1000s of rows and the excel calculation would take time as the file has other sheet calculations & 3rd party tolls connected. The aim is to fix as much as possible with Vba :) 

    NewBInVB

    Thursday, November 3, 2016 9:54 PM