Perform percentile calculations within a VBA individually on rows with a changing range

Question

Firstly I'm having issues setting up the percentile application not sure what to put as array and as myVar. I've had a look at a few codes and not sure what and why they put certain values there.

Secondly I want two different percentile calculations to run on rows 16-79 which each are unique to each other. The columns can't be set as the range changes from sheet to sheet. I have code below which finds the last column with data and creates 2 columns: "20th Percentile" and "80th Percentile" after it. So I'm thinking this can be used to help in setting up the range. This needs to be ran on multiple sheets.

Sub create_columns_test2()

    Dim WS As Worksheet
    Dim x As Integer

    Dim array(???) As Variant?
    'What do I set my array as? why variant?
    Dim myVar as Double?
    'What does double mean

    For Each WS In ActiveWorkbook.Worksheets

        With WS

            If .Cells.Find("20th Percentile") Is Nothing And _
               .Cells.Find("80th Percentile") Is Nothing Then

                x = .UsedRange.Columns.Count
                .Cells(16, x + 2).Value = "20th Percentile"
                myVar = Application.WorksheetFunction.Percentile(Array(16, x - 1), 0.2)
                .Cells(16, x + 3).Value = "80th Percentile"
                myVar = Application.WorksheetFunction.Percentile(Array(16, x - 2), 0.8)
                'I want the percentile to run from rows 16 to 79 individually
                    'from column C to the column before the matching column I've just created

            End If

        End With

    Next WS

  For Each WS In ActiveWorkbook.Worksheets
    WS.Range("N:Q").EntireColumn.AutoFit
  Next WS

End Sub

[Here](https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/data-type-summary) is the description of data types. Could you add some screenshots of your data, 'cos I'm not sure I understand correct what `.Cells.Find("20th Percentile")` does. And [here](https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/variant-data-type) is explanation on Variant. — Vitaliy Prushak, Jan 10 '20 at 09:35
That part of the code just searches the sheet for "20th Percentile" and if it does find it then the code won't run on that sheet — Zacchini, Jan 13 '20 at 05:36
because my array will change from sheet to sheet, do I make it a dynamic array? — Zacchini, Jan 13 '20 at 05:48

Vitaliy Prushak · Answer 1 · 2020-01-14T07:51:16.050

Check this solution. Read comments in code, if something isn't clear - ask in comments.
First note - I didn't quite understand how would you like to use percentile in the manner:

I want the percentile to run from rows 16 to 79 individually

but I think you will be able to fix function's arguments.

Sub create_columns_test2()

    Dim WS As Worksheet

    ' give variables proper names - I've used colNo instead of x for column number (see Note 1 in post)
    Dim colNo As Long
    Dim rowNo As Long

    ' Don't need this at all
'    Dim array(???) As Variant?
'    'What do I set my array as? why variant?
'    Dim myVar as Double?
'    'What does double mean


    ' be careful with using ActiveWorkbook / Sheet (see Note 2 in post)
    For Each WS In ActiveWorkbook.Worksheets

        With WS

            If .Cells.Find("20th Percentile") Is Nothing And _
               .Cells.Find("80th Percentile") Is Nothing Then


                ' using .UsedRange.Columns.Count is very tricky and may
                ' return incorrect data
                colNo = .Cells(1, Columns.Count).End(xlToLeft).Column

                ' Set column headers - Ok
                .Cells(16, colNo + 2).Value = "20th Percentile"
                .Cells(16, colNo + 3).Value = "80th Percentile"

                'I want the percentile to run from rows 16 to 79 individually
                    For rowNo = 17 To 79
                         'from column C to the column before the matching column I've just created

                         ' this part takes data from column C:
                         .Cells(rowNo, colNo + 2).Value = Application.WorksheetFunction.Percentile(Range(.Cells(rowNo, 3), .Cells(rowNo, colNo)), 0.2)
                         .Cells(rowNo, colNo + 3).Value = Application.WorksheetFunction.Percentile(Range(.Cells(rowNo, 3), .Cells(rowNo, colNo)), 0.8)
                    Next
            End If

        End With

    ' Don't need separate loop - can do it after each iteration
    WS.Range("N:Q").EntireColumn.AutoFit
    Next WS


End Sub

Note 1
Refer to Naming Conventions. There are also some other naming conventions in the web, which are like "for vba" and suggest to use a prefix based on type name, but personally I am against of that. But in general, those also mean one simple thing - when you get back to your code in a month - you won't remember at once what that variable x or y means, but you will definitely know what colNo or rowNo are used for.

Note 2
Check this post and decide whether you really want to use ActiveWorkbook here, because the workbook where the code is placed not always an active one.

Get a "Run-time error '1004': Unable to get the Percentile property of the WorksheetFunction class" on line `.Cells(rowNo, colNo + 2).Value = Application.WorksheetFunction.Percentile(.Cells(rowNo, 3), 0.2)` — Zacchini, Jan 14 '20 at 00:09

Perform percentile calculations within a VBA individually on rows with a changing range

1 Answers1