kelly mort
Well-known Member
- Joined
- Apr 10, 2017
- Messages
- 2,169
- Office Version
- 2016
- Platform
- Windows
This request is following the solution provided by @Akuini in post #35 at:
I just run into a problem and I will require your help to fix it.
I was previously dealing with whole numbers but now I am using both whole numbers and decimals.
Then when I run the code, it fails to give me the ranks for the floating point numbers. It produces blanks for all the decimal numbers.
So I changed the types of the variables z& to z# and Rnk& to Rnk# but the issue is still not resolved.
I have been staring at the screen since – but I can’t seem to fix it.
How do I get around with that?
This part of the code is storing the various numbers to be used like 100, 90, 75 etc.
The rest of the code is below. And this is the full code that I have been using for the ranking.
Function GetSuffix(Rnk#) As String
Dim sSuffix$
If Rnk Mod 100 >= 11 And Rnk Mod 100 <= 20 Then
sSuffix = " th"
Else
Select Case (Rnk Mod 10)
Case 1: sSuffix = " st"
Case 2: sSuffix = " nd"
Case 3: sSuffix = " rd"
Case Else: sSuffix = " th"
End Select
End If
GetSuffix = sSuffix
End Function
Ranking Code Amendment With System Reserved Numbers -vba
I want to set some numbers as system numbers to use against the ranking as done by the code below. Currently, I am using three numbers – 100, 95 and 90. In the future, I may reduce (say 2 numbers) or increase (say 5 or more numbers). And in each scenario, the aim is to make those numbers take...
www.mrexcel.com
I just run into a problem and I will require your help to fix it.
I was previously dealing with whole numbers but now I am using both whole numbers and decimals.
Then when I run the code, it fails to give me the ranks for the floating point numbers. It produces blanks for all the decimal numbers.
So I changed the types of the variables z& to z# and Rnk& to Rnk# but the issue is still not resolved.
I have been staring at the screen since – but I can’t seem to fix it.
How do I get around with that?
This part of the code is storing the various numbers to be used like 100, 90, 75 etc.
Code:
ary = Split(Trim(SysScore))
The rest of the code is below. And this is the full code that I have been using for the ranking.
Code:
Sub RankSystem()
Dim d As Object, m&, qr&, qq&, hh&, xq&, xz&, ary, i&
Application.ScreenUpdating = False
With Sheets("Data")
qq = .Range("C" & Rows.Count).End(xlUp).Row
For qr = 7 To qq
m = WorksheetFunction.CountIf(.Range("C7:C" & qq), .Range("C" & qr))
ary = Split(Trim(SysScore))
ary = Application.Transpose(Application.Transpose(ary))
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(ary)
d(CLng(ary(i))) = Empty
Next i
Call toRank(qr, m, d, 4, 13, ary)
hh = 0
For xq = qr To qr + m - 1
xz = WorksheetFunction.CountA(.Range("D" & xq & ":" & "M" & xq))
If xz > hh Then hh = xz
Next xq
ary = Split(Trim(SysScore))
For i = 0 To UBound(ary)
ary(i) = ary(i) * hh
Next i
ary = Application.Transpose(Application.Transpose(ary))
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(ary)
d(CLng(ary(i))) = Empty
Next i
Call toRank(qr, m, d, 14, 14, ary)
qr = qr + m - 1
Next qr
End With
Application.ScreenUpdating = True
End Sub
Code:
Sub toRank(qr&, m&, d As Object, a&, b&, ary)
Dim i&, z#, N&, yy&, g&, arz, arb
Dim e As Object, f As Object, c As Range, r As Range, ra As Range
With Sheets("Data")
For g = a To b
Set r = .Cells(qr, g).Resize(m)
yy = WorksheetFunction.CountA(r)
i = 0
If yy > 0 Then
ReDim arb(1 To yy)
For Each ra In r
If Len(ra) > 0 Then
i = i + 1
arb(i) = ra
End If
Next ra
Else
GoTo skip:
End If
ReDim arz(1 To UBound(arb))
For i = 1 To UBound(arb)
arz(i) = WorksheetFunction.Large(arb, i)
Next i
N = arz(UBound(arz))
Set e = CreateObject("scripting.dictionary")
For i = 1 To UBound(arz)
e(arz(i)) = e(arz(i)) + 1
Next i
Set f = CreateObject("scripting.dictionary")
z = 1
For i = ary(1) To N Step -1
If d.exists(i) And Not e.exists(i) Then
z = z + 1
End If
'
If e.exists(i) Then
f(i) = z & GetSuffix(z)
z = z + e(i)
End If
Next i
For Each c In .Cells(qr, g).Resize(m)
If f.exists(c.Value) Then c.Offset(, 14) = f(c.Value)
Next c
skip:
Next g
End With
End Sub
Function GetSuffix(Rnk#) As String
Dim sSuffix$
If Rnk Mod 100 >= 11 And Rnk Mod 100 <= 20 Then
sSuffix = " th"
Else
Select Case (Rnk Mod 10)
Case 1: sSuffix = " st"
Case 2: sSuffix = " nd"
Case 3: sSuffix = " rd"
Case Else: sSuffix = " th"
End Select
End If
GetSuffix = sSuffix
End Function