Percent rank ignoring NA in VBA

[Rookieeee]

Hello guys,

I am ranking stocks using VBA rather than excel. In my dataset, I have 1 column of figures and some NAs, this I want to use percentrank to sort these numbers, ignoring NA. I can do is in excel using =PERCENTRANK.INC(IF(ISNUMBER(Column), column, ""), the target cell). However, it does not work in VBA when I copy it. Can you guys help me out?

 monthlydata.Columns(4).Cells(i).Value = WorksheetFunction.PercentRank_Inc(IIf(IsNumber(monthlydata.Columns(3)), monthlydata.Columns(3), ""), monthlydata.Columns(3).Cells(i))

Thank you so much

 

[Fluff]

Hi & welcome to MrExcel
Easiest way to get the formula, is to put it into Excel, then start the macro recorder, select the cell & press F2 followed by enter. Turn off the recorder & you have the code.

 

[Rookieeee]

Hi & welcome to MrExcel
Easiest way to get the formula, is to put it into Excel, then start the macro recorder, select the cell & press F2 followed by enter. Turn off the recorder & you have the code.

Thank you for your help. The function in excel is not the same as what I use in VBA, it is just an example. In VBA, I have 'For' command, so VBA shows a syntax error when I use isnumber ().

 For i = 1 To 151

    monthlydata.Columns(4).Cells(i).Value = PercentRank.INC(IIf(IsNumber(monthlydata.Columns(3)), monthlydata.Columns(3), ""), monthlydata.Columns(3).Cells(i))
 
    Next i

This command, particularly isnumber function is the bug, and I don't know how I can solve that.

 

[Fluff]

What code does the recorder produce?

 

[Rookieeee]

Sub

 Macro1()

'
' Macro1 Macro
'


'
    Range("T2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-1]),PERCENTRANK.INC(IF(ISNUMBER(R2C19:R1422C19),R2C19:R1422C19),RC[-1]),"""")"
    Range("T3").Select
End Sub

this is what it produces

 

[Rookieeee]

What code does the recorder produce?

    Range("T2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-1]),PERCENTRANK.INC(IF(ISNUMBER(R2C19:R1422C19),R2C19:R1422C19),RC[-1]),"""")"
    Range("T3").Select
End Sub

this is what it produces, but it is only for the first cell of that column. I need to repeat this step for each cells

 

[Fluff]

In that case use

 Range("T2:T[COLOR=#ff0000]100[/COLOR]").FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-1]),PERCENTRANK.INC(IF(ISNUMBER(R2C19:R1422C19),R2C19:R1422C19),RC[-1]),"""")"

Change the value in red to the last row you want the formula.

 

[Rookieeee]

Thank you so much. You saved my life

 

[Fluff]

You're welcome & thanks for the feedback