Sorting a range of values correctly

Question

The code for the question "Sorting a range of values correctly in VBA" is below:

'Legend
Worksheets(1).Range("U1").Value = "Legend"
Worksheets(1).Range("U1:V1").Merge

'Classified Cases in Ranges
Worksheets(1).Range("U2:V2").Merge
Worksheets(1).Range("U2").Value = "Classified Cases in Ranges"

'Format the Legend
Worksheets(1).Range("U3:U40").NumberFormat = "@"
Worksheets(1).Range("V3:V40").NumberFormat = "@"

'Legend values
Worksheets(1).Range("U3").Value = 0
Worksheets(1).Range("U4").Value = 1
Worksheets(1).Range("U5").Value = 2
Worksheets(1).Range("U6").Value = 3
Worksheets(1).Range("U7").Value = 4
Worksheets(1).Range("U8").Value = 5
Worksheets(1).Range("U9").Value = 6
Worksheets(1).Range("U10").Value = 7
Worksheets(1).Range("U11").Value = 8
Worksheets(1).Range("U12").Value = 9
Worksheets(1).Range("U13").Value = 10
Worksheets(1).Range("U14").Value = 11
Worksheets(1).Range("U15").Value = 12
Worksheets(1).Range("U16").Value = 13
Worksheets(1).Range("U17").Value = 14
Worksheets(1).Range("U18").Value = 15
Worksheets(1).Range("U19").Value = 16
Worksheets(1).Range("U20").Value = 17
Worksheets(1).Range("U21").Value = 18
Worksheets(1).Range("U22").Value = 19
Worksheets(1).Range("U23").Value = 20
Worksheets(1).Range("U24").Value = 21
Worksheets(1).Range("U25").Value = 22
Worksheets(1).Range("U26").Value = 23
Worksheets(1).Range("U27").Value = 24
Worksheets(1).Range("U28").Value = 25
Worksheets(1).Range("U29").Value = 26
Worksheets(1).Range("U30").Value = 27
Worksheets(1).Range("U31").Value = 28
Worksheets(1).Range("U32").Value = 29
Worksheets(1).Range("U33").Value = 30
Worksheets(1).Range("U34").Value = 31
Worksheets(1).Range("U35").Value = 32
Worksheets(1).Range("U36").Value = 33
Worksheets(1).Range("U37").Value = 34
Worksheets(1).Range("U38").Value = 35
Worksheets(1).Range("U39").Value = 36
Worksheets(1).Range("U40").Value = "(blank)"

'Classified Cases in Ranges values
Worksheets(1).Range("V3").Value = "0-9"
Worksheets(1).Range("V4").Value = "10-19"
Worksheets(1).Range("V5").Value = "20-29"
Worksheets(1).Range("V6").Value = "30-39"
Worksheets(1).Range("V7").Value = "40-49"
Worksheets(1).Range("V8").Value = "50-59"
Worksheets(1).Range("V9").Value = "60-69"
Worksheets(1).Range("V10").Value = "70-79"
Worksheets(1).Range("V11").Value = "80-89"
Worksheets(1).Range("V12").Value = "90-99"
Worksheets(1).Range("V13").Value = "100-109"
Worksheets(1).Range("V14").Value = "110-119"
Worksheets(1).Range("V15").Value = "120-129"
Worksheets(1).Range("V16").Value = "130-139"
Worksheets(1).Range("V17").Value = "140-149"
Worksheets(1).Range("V18").Value = "150-159"
Worksheets(1).Range("V19").Value = "160-169"
Worksheets(1).Range("V20").Value = "170-179"
Worksheets(1).Range("V21").Value = "180-189"
Worksheets(1).Range("V22").Value = "190-199"
Worksheets(1).Range("V23").Value = "200-209"
Worksheets(1).Range("V24").Value = "210-219"
Worksheets(1).Range("V25").Value = "220-229"
Worksheets(1).Range("V26").Value = "230-239"
Worksheets(1).Range("V27").Value = "240-249"
Worksheets(1).Range("V28").Value = "250-259"
Worksheets(1).Range("V29").Value = "260-269"
Worksheets(1).Range("V30").Value = "270-279"
Worksheets(1).Range("V31").Value = "280-289"
Worksheets(1).Range("V32").Value = "290-299"
Worksheets(1).Range("V33").Value = "300-309"
Worksheets(1).Range("V34").Value = "310-319"
Worksheets(1).Range("V35").Value = "320-329"
Worksheets(1).Range("V36").Value = "330-339"
Worksheets(1).Range("V37").Value = "340-349"
Worksheets(1).Range("V38").Value = "350-359"
Worksheets(1).Range("V39").Value = ">=360"
Worksheets(1).Range("V40").Value = "(blank)"


'***************************************************
'***************************************************
'***************************************************

'Move to US MACRO worksheet
Worksheets(2).Activate

'Pivot Table 1
'Pivot Table 1
'Pivot Table 1

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'Use vlookup() function and perform conditional formatting
For x = 4 To (LastRow - 1)
Cells(x, 1).FormulaR1C1 = "=VLOOKUP(RC[1],'US Master Macro'!R[-1]C[20]:R[36]C[21],2,FALSE)"
If Cells(x, 2) >= 8 Then
Cells(x, 1).Interior.ColorIndex = 3
Cells(x, 1).Font.ThemeColor = xlThemeColorDark1
Cells(x, 1).Font.Bold = True
Cells(x, 2).Interior.ColorIndex = 3
Cells(x, 2).Font.ThemeColor = xlThemeColorDark1
Cells(x, 2).Font.Bold = True
Cells(x, 3).Interior.ColorIndex = 3
Cells(x, 3).Font.ThemeColor = xlThemeColorDark1
Cells(x, 3).Font.Bold = True
Else
Cells(x, 1).Interior.ColorIndex = 2
End If
Next x

"I have the following data in a pivot table. To the left, is described a range and to the right the number of cells within that range. I would like to learn a code that correctly arranges the range in the left and color codes anything after "80-89". I have used the sort function but it doesn't seem to work. Please help."

Days   Count of PR ID
"10-19"     656
"0-9"       480
"20-29"     190
"30-39"     115
"40-49"     65
"50-59"     47
"70-79"     28
"60-69"     23
"80-89"     12
"110-119"   3
"120-129"   2
"130-139"   1
"100-109"   1
"180-189"   1

Instead of posting the code you're working with as an Answer, please edit it in to your original post. Edit: Why this double post? This is exactly your other question, without any additional information or apparent difference. Are you trying to ask about the code you've posted? Can you please clarify any difference? Is there a question or are you simply posting the code? Also "...it doesn't seem to work" doesn't help us in troubleshooting anything. *How* doesn't it work? — BruceWayne, Jun 06 '18 at 19:52
Possible duplicate of [Sorting a range of values correctly in vba](https://stackoverflow.com/questions/50663504/sorting-a-range-of-values-correctly-in-vba) — algrid, Jun 06 '18 at 20:15

Sorting a range of values correctly

0 Answers0