Sort code not sorting correctly

[ipbr21054]

Morning,

I am using the code supplied below but noticed it doesnt sort correctly.
Do you see why or can you advise another code.

I have supplied some screen shot example of its sorting issue.

Private Sub CommandButton1_Click()
Dim x As Long
Dim ws As Worksheet
Set ws = Sheets("MC LIST")
Dim SortColumn As String

Select Case ComboBox1
Case "CUSTOMER"
SortColumn = "A"

Case "VIN"
SortColumn = "B"

Case "MAKE"
SortColumn = "C"

Case "MODEL"
SortColumn = "D"

Case "YEAR"
SortColumn = "E"

Case "ITEM BOUGHT"
SortColumn = "F"

Case "CHIP"
SortColumn = "G"

Case "COUNTRY"
SortColumn = "H"

Case "ORIGINAL PN"
SortColumn = "I"

End Select
If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A7:I" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess
End With
End If
Unload AtoZMcList
End Sub
Private Sub UserForm_Initialize()
Dim rheadings As Range
Dim cl As Range
Set rheadings = Worksheets("MC LIST").Range("A6:I6")
For Each cl In rheadings
Me.ComboBox1.AddItem cl.Value
Next cl
End Sub

 

[Yongle]

Try this

' INSERT THIS LINE
     .Sort.SortFields.Clear
' ABOVE
     .Range("A7:I" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess

 

[mole999]

do some of your data fields have spaces before the text, though I doubt that is the only answer

If you change
Case "YEAR"
SortColumn = "E"
to
Case "YEAR"
SortColumn = 5

does that make a difference

 

[sykes]

It looks as if you're passing a string (SortColumn) to this line:

.Range("A7:I" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess

...where I think SortColumn might need to be an integer - to pass the column number to the sortKey.

 

[ipbr21054]

Well i cant explain,but after making a change & nothing happened a pc restart now shows it all ok ?

many thanks for the replies.

 

[sykes]

Great!!

 

[mole999]

Well i cant explain,but after making a change & nothing happened a pc restart now shows it all ok ?

many thanks for the replies.

may be related to other code like application.enablevents or application.screenupdating or application.calculate that could have been turned off, and a restart will clear those down. I often add a recover sub somewhere in my modules

sub recover
with application
.screenupdating = true
.enableevents = true
end with
end sub

just to quickly reset what i have been trying