VBA PercentRank Using Array of Textbox Values

[breynolds0431]

Hi. I have a userform that will count occurrences from a sheet based on a few factors, most notably being a date range. The below has a date range of one week in the past to current date. The counts are working as they should. However, I was trying to find a way to rank these countifs. The countifs results currently range from 1 up to 4. The error that's returned is "unable to get the percentrank property of the worksheetfunction class".

Any ideas on how to get this to work in the below or another way to "rank" the countifs results?

With Me

          .tbL1AsgCnt1.Value = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud1), "#,##0")
          .tbL1AsgCnt2.Value = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud2), "#,##0")
          .tbL1AsgCnt3.Value = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud3), "#,##0")
          .tbL1AsgCnt4.Value = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud4), "#,##0")
          .tbL1AsgCnt5.Value = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud5), "#,##0")
          
          Dim arr: arr = Array(.tbL1AsgCnt1.Value, .tbL1AsgCnt2.Value, .tbL1AsgCnt3.Value, .tbL1AsgCnt4.Value, .tbL1AsgCnt5.Value)

          .tbL1Shr1.Value = Format(1 - WorksheetFunction.PercentRank(arr, .tbL1AsgCnt1.Value), "#0%")
          .tbL1Shr2.Value = Format(1 - WorksheetFunction.PercentRank(arr, .tbL1AsgCnt2.Value), "#0%")
          .tbL1Shr3.Value = Format(1 - WorksheetFunction.PercentRank(arr, .tbL1AsgCnt3.Value), "#0%")
          .tbL1Shr4.Value = Format(1 - WorksheetFunction.PercentRank(arr, .tbL1AsgCnt4.Value), "#0%")
          .tbL1Shr5.Value = Format(1 - WorksheetFunction.PercentRank(arr, .tbL1AsgCnt5.Value), "#0%")

End With

 

[breynolds0431]

I figured this out. The array needs to be populated with other variables. Not sure what the rules are about marking your own answer as the answer but think that's necessary so this post doesn't show up in the Unanswered threads.

          Dim L1AsgCnt1 As Integer: L1AsgCnt1 = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud1), "#,##0")
          Dim L2AsgCnt1 As Integer: L2AsgCnt1 = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud2), "#,##0")
          Dim L3AsgCnt1 As Integer: L3AsgCnt1 = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud3), "#,##0")
          Dim L4AsgCnt1 As Integer: L4AsgCnt1 = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud4), "#,##0")
          Dim L5AsgCnt1 As Integer: L5AsgCnt1 = Format(WorksheetFunction.CountIfs(rnpr, "", Grant, "<>Denied", assDt, ">=" & Date - 7, assDt, "<=" & Date, L1All, Aud5), "#,##0")

          .tbL1AsgCnt1.Value = L1AsgCnt1
          .tbL1AsgCnt2.Value = L2AsgCnt1
          .tbL1AsgCnt3.Value = L3AsgCnt1
          .tbL1AsgCnt4.Value = L4AsgCnt1
          .tbL1AsgCnt5.Value = L5AsgCnt1
          
          Dim arr: arr = Array(L1AsgCnt1, L2AsgCnt1, L3AsgCnt1, L4AsgCnt1, L5AsgCnt1)

          .tbL1Shr1.Value = Format(1 - Application.WorksheetFunction.PercentRank(arr, L1AsgCnt1), "#0%")
          .tbL1Shr2.Value = Format(1 - Application.WorksheetFunction.PercentRank(arr, L2AsgCnt1), "#0%")
          .tbL1Shr3.Value = Format(1 - Application.WorksheetFunction.PercentRank(arr, L3AsgCnt1), "#0%")
          .tbL1Shr4.Value = Format(1 - Application.WorksheetFunction.PercentRank(arr, L4AsgCnt1), "#0%")
          .tbL1Shr5.Value = Format(1 - Application.WorksheetFunction.PercentRank(arr, L5AsgCnt1), "#0%")

 

[Peter_SSs]

Not sure what the rules are about marking your own answer as the answer

There is no issue with that provided that you actually provide the solution in your post - which you did.