My smart tweak ended up crashing excel- I need expert advice and fix

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
I have a table layout as below, it used to work with the code below until I inserted the column under “M” then replaced all 12 with the 13 you see in the code.


Code:
     For i = 2 To 13


And


Code:
       rCell.Offset(, 13).Value = Rnk & GetOrdinalSuffixForRank(Rnk)


I was thinking that will fix the new change, just to face issues with excel, crashing when I run the code. This was not happening before.


Also, since my data starts from row 7, I want those with the big minds help me out by pointing out any potential trap in my code for me. It was a code I had from this same forum some time ago, I have adjusted it, but I am thinking there is something that I am not doing right. If that’s true, then, somebody should help me out.


So the data table from A7 to last used row in column O. The above rows are headers. I want to replace that usedRange property in the code and use the slightly static range as I pointed out in the comment. The ranks for column D under column Q, and it follows as the data table.


Please help me out fix it. Thanks in advance
Code:
6    C    D    E    F    G    H    I    J    K    L    M    N    O
7    x    34    27    43    45    37    34    31    28    25    56    67    371
8    x    48    45    23    39    23    33    30    27    24    98    55    292
9    y    47    40    33    26    19    12    25    23    15    83    55    240
10    y    46    23    25    23    15    10    23    20    13    81    55    198
11    z    35    28    21    14    7    5    17    13    5    23    12    145

Code:
Sub MyRank()
     Dim dicSection As Object, vItem As Variant, wsData As Worksheet, vSection As Variant, rScore As Range, _
     rCell As Range, Score As Variant, Rnk As Double, LastRow&, iCol&
     Application.ScreenUpdating = False
     
    Set wsData = Sheets("Sheet1")
    With wsData
        If .FilterMode Then .ShowAllData
        LastRow = .Cells(Rows.Count, "D").End(xlUp).Row
    End With
    
    
    On Error Resume Next
    Set dicSection = CreateObject("Scripting.Dictionary")
    dicSection.CompareMode = 1 'vbTextCompare
    vSection = wsData.Range("C6:C" & LastRow).Value
    For i = LBound(vSection) + 1 To UBound(vSection)
        If Not dicSection.Exists(vSection(i, 1)) Then
            dicSection(vSection(i, 1)) = ""
        End If
     Next i
For Each vItem In dicSection.keys()
    With wsData. UsedRange ‘ I want to use semi-static range here – like “range(“A7:O” & lastrow)
    .AutoFilter field:=3, Criteria1:=vItem
    
    Set rScore = .Offset(1, 1).Resize(Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
    For i = 2 To 13
        For Each rCell In rScore.Offset(, i)
        Score = rCell.Value
            If Application.IsNumber(Score) Then
                Rnk = WorksheetFunction.Rank(CDbl(Score), rScore.Offset(, i))
                rCell.Offset(, 13).Value = Rnk & GetOrdinalSuffixForRank(Rnk)
            End If
        Next rCell
    Next i
        .AutoFilter
    End With
    Next vItem
    Application.ScreenUpdating = True
    
    Set dicSection = Nothing
    Set rScore = Nothing
    Set rCell = Nothing
    
    Exit Sub
    On Error GoTo 0
End Sub


Function GetOrdinalSuffixForRank(Rnk As Double) 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
     GetOrdinalSuffixForRank = sSuffix
End Function
 
Last edited:
How about this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:24.71px;" /><col style="width:30.42px;" /><col style="width:28.51px;" /><col style="width:28.51px;" /><col style="width:30.42px;" /><col style="width:30.42px;" /><col style="width:25.66px;" /><col style="width:26.61px;" /><col style="width:29.47px;" /><col style="width:27.56px;" /><col style="width:33.27px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:11.41px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.97px;" /><col style="width:38.02px;" /><col style="width:38.97px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:40.87px;" /><col style="width:32.32px;" /><col style="width:31.37px;" /><col style="width:32.32px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff00; ">C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >x</td><td style="text-align:right; ">34</td><td style="text-align:right; ">27</td><td style="text-align:right; ">43</td><td style="text-align:right; ">45</td><td style="text-align:right; ">37</td><td style="text-align:right; ">34</td><td style="text-align:right; ">31</td><td style="text-align:right; ">28</td><td style="text-align:right; ">25</td><td style="text-align:right; ">56</td><td style="text-align:right; ">67</td><td style="text-align:right; ">371</td><td > </td><td >2 ND</td><td >2 ND</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >2 ND</td><td >1 ST</td><td >1 ST</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >x</td><td style="text-align:right; ">48</td><td style="text-align:right; ">45</td><td style="text-align:right; ">23</td><td style="text-align:right; ">39</td><td style="text-align:right; ">23</td><td style="text-align:right; ">33</td><td style="text-align:right; ">30</td><td style="text-align:right; ">27</td><td style="text-align:right; ">24</td><td style="text-align:right; ">98</td><td style="text-align:right; ">55</td><td style="text-align:right; ">292</td><td > </td><td >1 ST</td><td >1 ST</td><td >2 ND</td><td >2 ND</td><td >2 ND</td><td >2 ND</td><td >2 ND</td><td >2 ND</td><td >2 ND</td><td >1 ST</td><td >2 ND</td><td >2 ND</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >y</td><td style="text-align:right; ">47</td><td style="text-align:right; ">40</td><td style="text-align:right; ">33</td><td style="text-align:right; ">26</td><td style="text-align:right; ">19</td><td style="text-align:right; ">12</td><td style="text-align:right; ">25</td><td style="text-align:right; ">23</td><td style="text-align:right; ">15</td><td style="text-align:right; ">83</td><td style="text-align:right; ">55</td><td style="text-align:right; ">240</td><td > </td><td >2 ND</td><td >3 RD</td><td >2 ND</td><td >3 RD</td><td >2 ND</td><td >3 RD</td><td >2 ND</td><td >3 RD</td><td >2 ND</td><td >3 RD</td><td >3 RD</td><td >3 RD</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >y</td><td style="text-align:right; ">46</td><td style="text-align:right; ">23</td><td style="text-align:right; ">25</td><td style="text-align:right; ">23</td><td style="text-align:right; ">15</td><td style="text-align:right; ">10</td><td style="text-align:right; ">23</td><td style="text-align:right; ">20</td><td style="text-align:right; ">13</td><td style="text-align:right; ">81</td><td style="text-align:right; ">55</td><td style="text-align:right; ">198</td><td > </td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >4 TH</td><td >3 RD</td><td >4 TH</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >y</td><td style="text-align:right; ">57</td><td style="text-align:right; ">50</td><td style="text-align:right; ">43</td><td style="text-align:right; ">36</td><td style="text-align:right; ">29</td><td style="text-align:right; ">22</td><td style="text-align:right; ">35</td><td style="text-align:right; ">33</td><td style="text-align:right; ">25</td><td style="text-align:right; ">93</td><td style="text-align:right; ">65</td><td style="text-align:right; ">250</td><td > </td><td >1 ST</td><td >2 ND</td><td >1 ST</td><td >2 ND</td><td >1 ST</td><td >2 ND</td><td >1 ST</td><td >2 ND</td><td >1 ST</td><td >2 ND</td><td >1 ST</td><td >2 ND</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >y</td><td style="text-align:right; ">47</td><td style="text-align:right; ">51</td><td style="text-align:right; ">26</td><td style="text-align:right; ">37</td><td style="text-align:right; ">16</td><td style="text-align:right; ">23</td><td style="text-align:right; ">24</td><td style="text-align:right; ">34</td><td style="text-align:right; ">14</td><td style="text-align:right; ">94</td><td style="text-align:right; ">56</td><td style="text-align:right; ">251</td><td > </td><td >2 ND</td><td >1 ST</td><td >3 RD</td><td >1 ST</td><td >3 RD</td><td >1 ST</td><td >3 RD</td><td >1 ST</td><td >3 RD</td><td >1 ST</td><td >2 ND</td><td >1 ST</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >z</td><td style="text-align:right; ">35</td><td style="text-align:right; ">28</td><td style="text-align:right; ">21</td><td style="text-align:right; ">14</td><td style="text-align:right; ">7</td><td style="text-align:right; ">5</td><td style="text-align:right; ">17</td><td style="text-align:right; ">13</td><td style="text-align:right; ">5</td><td style="text-align:right; ">23</td><td style="text-align:right; ">12</td><td style="text-align:right; ">145</td><td > </td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td><td >1 ST</td></tr></table>
Code:
Sub MyRank()
  Dim dicSection As Object, vItem As Variant, wsData As Worksheet, vSection As Variant
  Dim rScore As Range, rCell As Range, Score As Variant, Rnk As Double, LastRow&, i As Long
  Application.ScreenUpdating = False
  Set wsData = Sheets("Sheet1")
  If wsData.FilterMode Then wsData.ShowAllData
  LastRow = wsData.Cells(Rows.Count, "C").End(xlUp).Row
  Set dicSection = CreateObject("Scripting.Dictionary")
  dicSection.CompareMode = 1 'vbTextCompare
  vSection = wsData.Range("C7:C" & LastRow)
  For i = 1 To UBound(vSection)
    dicSection(vSection(i, 1)) = ""
  Next i
  For Each vItem In dicSection.keys()
    With wsData.Range("C6:O" & LastRow)
      .AutoFilter field:=1, Criteria1:=vItem
        For i = 1 To 12
          Set rScore = .Offset(1, i).Resize(.Rows.Count, 1).SpecialCells(xlCellTypeVisible)
          For Each rCell In rScore
            Score = rCell.Value
            If Application.IsNumber(Score) Then
              Rnk = WorksheetFunction.Rank(CDbl(Score), rScore)
              rCell.Offset(, 13).Value = Rnk & GetOrdinalSuffixForRank(Rnk)
            End If
          Next rCell
        Next
      .AutoFilter
    End With
  Next vItem
  Application.ScreenUpdating = True
End Sub
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top