Sorting Data Code Issues

Big_Chew

New Member
Joined
Oct 15, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I swear this was working the other day but now for the life of me I cannot figure out what is wrong. Below is a snippet of my data set and the code I am trying to use.

1603201027353.png


VBA Code:
Private Sub CommandButton2_Click()
'Sorts Raw Data Button
'Sorts Raw Data on Equipment, Year and Period
    With ActiveSheet.Sort
'Clears sorting
    .SortFields.Clear
    .SortFields.Add Key = ("D7"), Order:=xlAscending
    .SortFields.Add Key = ("A7"), Order:=xlAscending
    .SortFields.Add Key = ("B7"), Order:=xlAscending
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    .SetRange Range("A7:L" & lr)
    .Header = xlNo
    .Apply

End With


End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What exactly is the issue?
What is it that you expect to see that is different from what you have shown?
 
Upvote 0
Sorry I should have been more clear. The image of the data is after I sorted it without using my vba code. When running the code I get a run time error 13, type mismatch. When I debug it takes me to the D7 line of code. Below is what the data set looks like prior to sorting.

1603210624959.png
 
Upvote 0
Can you possibly use this tool to post an excerpt of your data?

That allows us to copy/paste your data, so we can try to recreate the scenario on our side and try to debug it.
 
Upvote 0
The New and Improved Chew Sheet V1.2.xls
ABCDEFGHIJKL
6Year (start)PeriodDate StartEquipmentPanel NameLinear AdvanceLinear RetreatCTPFSulfurAshVolReflectance
72020AUG208/1/2020BE-U114610350.0####0.773#####0.993
82020AUG208/3/2020BE-U213571.7####0.627#####1.032
92020AUG208/20/2020BE-U222426.1####0.613#####1.032
102020DEC2012/1/2020BE-U134 <B:1>8551.6####0.744#####0.997
112020DEC2012/1/2020BE-U22005699.6####0.635#####1.017
122020DEC2012/31/2020BE-U134 <B:1>450.0####0.730#####0.998
132020DEC2012/31/2020BE-U2200299.4####0.639#####1.016
142020JUL207/1/2020BE-U11459534.9####0.738#####0.993
152020JUL207/13/2020BE-U213200.8####0.624#####1.027
162020JUL207/31/2020BE-U1146365.1####0.755#####0.993
172020NOV2011/1/2020BE-U134 <B:1>8100.3####0.772#####0.997
182020NOV2011/1/2020BE-U22005404.2####0.626#####1.022
192020OCT2010/1/2020BE-U11443553.8####0.809#####0.995
202020OCT2010/1/2020BE-U22007193.0####0.651#####1.028
212020OCT2010/10/2020BE-U134 <B:1>7244.7####0.795#####0.996
222020SEP209/1/2020BE-U11463890.2####0.798#####0.994
232020SEP209/1/2020BE-U226622.0####0.610#####1.035
242020SEP209/12/2020BE-U11446459.4####0.809#####0.995
252020SEP209/30/2020BE-U2200279.1####0.642#####1.028
262021APR214/1/2021BE-U135 <B:1>11950.6####0.676#####1.001
272021APR214/1/2021BE-U2SSA211500.0####0.691#####1.023
282021APR214/1/2021BE-U32042540.3####0.749#####1.001
292021APR214/8/2021BE-U320510339.7####0.756#####1.002
302021APR214/29/2021BE-U135 <1:E>922.5####0.671#####1.001
312021AUG218/1/2021BE-U135 <1:E>13432.8####0.775#####0.991
322021AUG218/1/2021BE-U2SSA312000.0####0.763#####1.004
332021AUG218/1/2021BE-U32063669.4####0.687#####1.003
342021AUG218/10/2021BE-U32139770.6####0.687#####1.005
352021DEC2112/1/2021BE-U150638.9####0.752#####0.977
362021DEC2112/1/2021BE-U2SSA49500.0####0.773#####1.008
372021DEC2112/1/2021BE-U321310640.0####0.799#####0.995
382021DEC2112/2/2021BE-U137 <B:1>9991.2####0.748#####0.976
392021DEC2112/31/2021BE-U137 <B:1>558.7####0.739#####0.974
402021DEC2112/31/2021BE-U2SSA4500.0####0.770#####1.005
412021DEC2112/31/2021BE-U3213560.0####0.802#####0.993
422021FEB212/1/2021BE-U134 <1:E>7494.1####0.704#####0.999
432021FEB212/1/2021BE-U2SSA12476.9####0.643#####1.017
442021FEB212/5/2021BE-U2SSA28616.8####0.640#####1.018
452021FEB212/22/2021BE-U135 <B:1>2405.9####0.700#####1.000
462021JAN211/1/2021BE-U134 <B:1>3491.7####0.725#####0.998
472021JAN211/1/2021BE-U22005347.8####0.645#####1.014
482021JAN211/12/2021BE-U134 <1:E>6408.3####0.714#####0.998
492021JAN211/25/2021BE-U2SSA12000.0####0.645#####1.015
502021JUL217/1/2021BE-U135 <1:E>12313.4####0.735#####0.995
512021JUL217/1/2021BE-U2SSA311000.0####0.746#####0.999
522021JUL217/1/2021BE-U32051566.7####0.712#####1.005
532021JUL217/7/2021BE-U320610781.9####0.702#####1.005
542021JUN216/1/2021BE-U135 <1:E>11753.7####0.700#####0.999
552021JUN216/1/2021BE-U2SSA310500.0####0.744#####1.008
562021JUN216/1/2021BE-U320511731.5####0.728#####1.005
572021MAR213/1/2021BE-U135 <B:1>12017.9####0.694#####1.000
582021MAR213/1/2021BE-U2SSA212500.0####0.658#####1.022
592021MAR213/24/2021BE-U32043920.0####0.744#####1.000
602021MAY215/1/2021BE-U135 <1:E>12313.4####0.677#####1.001
612021MAY215/1/2021BE-U2SSA21348.1####0.717#####1.021
622021MAY215/1/2021BE-U320512320.0####0.743#####1.004
632021MAY215/5/2021BE-U2SSA39651.9####0.741#####1.017
642021NOV2111/1/2021BE-U135 <1:E>2430.0####0.759#####0.979
652021NOV2111/1/2021BE-U2SSA31647.7####0.799#####1.018
662021NOV2111/1/2021BE-U321310640.0####0.782#####0.999
672021NOV2111/4/2021BE-U2SSA47852.3####0.792#####1.014
682021NOV2111/5/2021BE-U1508204.4####0.756#####0.979
692021OCT2110/1/2021BE-U135 <1:E>12873.1####0.767#####0.981
702021OCT2110/1/2021BE-U2SSA311500.0####0.795#####1.018
712021OCT2110/1/2021BE-U321312880.0####0.730#####1.003
722021SEP219/1/2021BE-U135 <1:E>12873.1####0.775#####0.986
732021SEP219/1/2021BE-U2SSA311500.0####0.776#####1.017
742021SEP219/1/2021BE-U321312880.0####0.697#####1.004
752022APR224/1/2022BE-U137 <B:1>12313.4####0.944#####0.955
762022APR224/1/2022BE-U2SS1911000.0####0.869#####1.004
772022APR224/1/2022BE-U321812320.0####0.881#####1.015
782022AUG228/1/2022BE-U13213993.3####0.849#####0.956
792022AUG228/1/2022BE-U2SS2512500.0####0.920#####0.999
802022AUG228/1/2022BE-U321613513.2####0.802#####1.005
812022AUG228/1/2022BE-U3217510.7####0.836#####1.015
822022DEC2212/1/2022BE-U13110634.3####0.914#####0.952
832022DEC2212/1/2022BE-U2SS249500.0####0.880#####0.997
842022DEC2212/1/2022BE-U3216538.9####0.802#####1.025
852022DEC2212/1/2022BE-U32207787.9####0.856#####1.023
862022DEC2212/20/2022BE-U32212313.2####0.750#####1.023
872022FEB222/1/2022BE-U137 <B:1>12313.4####0.762#####0.966
882022FEB222/1/2022BE-U2SS1911000.0####0.799#####0.992
892022FEB222/1/2022BE-U321812320.0####0.845#####1.001
902022JAN221/1/2022BE-U137 <B:1>12318.6####0.736#####0.971
912022JAN221/1/2022BE-U2SSA44774.6####0.769#####1.004
922022JAN221/1/2022BE-U32137090.2####0.800#####0.992
932022JAN221/14/2022BE-U2SSA5550.0####0.766#####1.002
942022JAN221/17/2022BE-U2SS195675.4####0.785#####0.988
952022JAN221/19/2022BE-U32185229.8####0.817#####0.996
962022JUL227/1/2022BE-U1304702.0####0.863#####0.960
972022JUL227/1/2022BE-U2SS259000.0####0.922#####1.003
982022JUL227/1/2022BE-U321710071.2####0.849#####1.011
992022JUL227/19/2022BE-U1325371.9####0.864#####0.958
1002022JUN226/1/2022BE-U13012887.8####0.908#####0.956
1012022JUN226/1/2022BE-U137 <B:1>548.5####0.922#####0.948
1022022JUN226/1/2022BE-U2SS191401.5####0.928#####1.013
1032022JUN226/1/2022BE-U321713427.2####0.837#####1.005
1042022JUN226/3/2022BE-U2SS2510598.5####0.935#####1.007
1052022MAR223/1/2022BE-U137 <B:1>13992.5####0.845#####0.961
1062022MAR223/1/2022BE-U2SS1912500.0####0.837#####0.998
1072022MAR223/1/2022BE-U321814000.0####0.868#####1.008
1082022MAY225/1/2022BE-U137 <B:1>12869.7####0.947#####0.950
1092022MAY225/1/2022BE-U2SS1911500.0####0.907#####1.010
1102022MAY225/1/2022BE-U32186156.4####0.914#####1.019
1112022MAY225/14/2022BE-U32176721.3####0.815#####1.000
1122022NOV2211/1/2022BE-U13110634.3####0.901#####0.952
1132022NOV2211/1/2022BE-U2SS249500.0####0.888#####1.000
1142022NOV2211/1/2022BE-U321610640.0####0.788#####1.023
1152022OCT2210/1/2022BE-U13210586.6####0.807#####0.968
1162022OCT2210/1/2022BE-U2SS254431.1####0.880#####0.991
1172022OCT2210/1/2022BE-U321612880.0####0.796#####1.018
1182022OCT2210/12/2022BE-U2SS247068.9####0.893#####1.004
1192022OCT2210/25/2022BE-U1312286.5####0.908#####0.954
1202022SEP229/1/2022BE-U13212873.1####0.828#####0.960
1212022SEP229/1/2022BE-U2SS2511500.0####0.913#####0.994
1222022SEP229/1/2022BE-U321612880.0####0.812#####1.011
Raw Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A7:A185,A363:A532Cell Value=A6textYES
 
Upvote 0
Can you tell me where you got this code?
I see a few issues with it right away, i.e. references like this:
VBA Code:
Key = ("D7")
need to be like this:
VBA Code:
Key1:=Range("D7")
but I think there are also other issues as well.

If you can point us to where exactly you got this code, it may help us debug all the problems with the code faster.

I have done multiple field sorting before, but never like this. I typically use this sort of structure:
VBA Code:
    Range("A1:E" & lr).Sort _
        Key1:=Range("E1"), Order1:=xlDescending, _
        Key2:=Range("A1"), order2:=xlAscending, Header:=xlYes
 
Upvote 0
Honestly off the internet somewhere, either a YouTube video or a website that I modified the code to work for my needs. I am not against changing the code all together as long as it would sort by D, A, and then B. Actually now that I think about it my computer restarted in the middle of the night to apply updates, would it be possible that my code was not saved?
 
Upvote 0
See if this does what you want:
VBA Code:
Private Sub CommandButton2_Click()
'Sorts Raw Data Button
'Sorts Raw Data on Equipment, Year and Period

    Dim lr As Long
    
    lr = Cells(Rows.Count, "A").End(xlUp).Row

    Range("A6:L" & lr).Sort _
        Key1:=Range("D6"), Order1:=xlAscending, _
        Key2:=Range("A6"), Order2:=xlAscending, _
        Key3:=Range("B6"), order3:=xlAscending, Header:=xlYes

End Sub
 
Upvote 0
Kind of. I need it to sort anything that looks like a number as a number. I need it to sort the month in chronological order.
 
Upvote 0
Kind of. I need it to sort anything that looks like a number as a number. I need it to sort the month in chronological order.
Are you referring to column B?
If so, that is a data issue, not a code issue.
It looks like all those entries are text, so are being treated as text.
If you want them to be sorted as dates, you would need to convert those to valid dates, or create a Custom sort (I have never done that myself, but know it exists).
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

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