VBA Code to Write Percentile Function

[nirvehex]

Hi,

I'm trying to write some VBA code that does the same thing as this Excel Code:

 {=PERCENTILE.INC(IF($A$2:LASTROW=A2,$AF$2:LASTROW),.5)}

I know of the function: [code]Application.WorksheetFunction.Percentile(

but not sure how to use it.

Can anyone help me out please?

Thanks!

 

[Rijnsent]

Hi Nirvehex,

something in this direction should work:

ActiveSheet.Evaluate = "=PERCENTILE.INC(IF($A$2:LASTROW=A2,$AF$2:LASTROW),.5)"

An alternative: try the macro recorder when you CTRL+SHIFT+ENTER your function and see what is recorded.
Cheers,

Koen

 

[nirvehex]

Hi Thanks Rinjnsent. I tried ActiveSheet.Evaluate. Unfortunately that didn't work. When I do the macro recorder, I get Row Column (RC) formatting which won't work either.

I feel like my code is so close. Here's what I have:

Sheets("WOs (DSP Only)").Select


    Range("A2").Select
    ActiveCell.Formula = "=CONCATENATE(L3," - ",K3," - ",Z3," - ",X3)"
    Selection.AutoFill Destination:=Range("A2:A & LastRow")
    
    Range("B2").Select
     ActiveCell.FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & Lastrow & "=A2,$AF$2:$A$" & Lastrow & "),.5)"
     Selection.AutoFill Destination:=Range("B2:B & LastRow")

Any other thoughts?

Thanks!
-Mark

 

[Domenic]

You have a number of issues regarding the double quotes. Also, there's no need to use the Select method. Try the following instead...

    With Sheets("WOs (DSP Only)")
        .Range("A2").Formula = "=CONCATENATE(L3,"" - "",K3,"" - "",Z3,"" - "",X3)"
        .Range("A2").AutoFill Destination:=.Range("A2:A" & LastRow)
        .Range("B2").FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & LastRow & "=A2,$AF$2:$AF$" & LastRow & "),.5)"
        .Range("B2").AutoFill Destination:=.Range("B2:B" & LastRow)
    End With

And, of course, you can always select/activate your sheet at the end, if so desired.

Hope this helps!

P.S. Note that I corrected the second range being referenced in your array formula to what I believe should be $AF$2:$AF. Hopefully, this was your intent. If not, please ignore the change.

 

[nirvehex]

Thanks Domenic and nice catch on my typo. This code is so much cleaner and worked perfectly.