Sort code advice please

[ipbr21054]

Hi,
I have the code below which im trying to edit but getting a little stuck.

This was the original code which worked fine,columns A-F would sort A-Z & also column G of which was DATE would sort OLD DATE - NEW DATE

Private Sub CommandButton1_Click()

Dim x As Long

Dim ws As Worksheet

Set ws = Sheets("HONDA LIST")

Dim SortColumn As String





Select Case ComboBox1

Case "VIN NUMBER"

SortColumn = "A"



Case "VEHICLE"

SortColumn = "B"



Case "CUSTOMER"

SortColumn = "C"



Case "YEAR"

SortColumn = "D"



Case "HONDA NUMBER"

SortColumn = "E"



Case "SUPPLIED"

SortColumn = "F"



Case "DATE"

SortColumn = "G"



End Select



If ComboBox1 = "VIN NUMBER" Then

Range("A4").Select



ElseIf ComboBox1 = "VEHICLE" Then

Range("B4").Select



ElseIf ComboBox1 = "CUSTOMER" Then

Range("C4").Select



ElseIf ComboBox1 = "YEAR" Then

Range("D4").Select



ElseIf ComboBox1 = "HONDA NUMBER" Then

Range("E4").Select



ElseIf ComboBox1 = "SUPPLIED" Then

Range("F4").Select



ElseIf ComboBox1 = "DATE" Then

Range("G4").Select

End If



If Len(SortColumn) <> 0 Then

Application.ScreenUpdating = False

End If



With ws

If .AutoFilterMode Then .AutoFilterMode = False

x = .Cells(.Rows.Count, 1).End(xlUp).Row

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

End With



Unload Me

End Sub

I would like for the DATE to sort NEW - OLD
Ive tried to edit the above but running into a few run time errors.

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


Select Case ComboBox1
Case "VIN NUMBER"
SortColumn = "A"

Case "VEHICLE"
SortColumn = "B"

Case "CUSTOMER"
SortColumn = "C"

Case "YEAR"
SortColumn = "D"

Case "HONDA NUMBER"
SortColumn = "E"

Case "SUPPLIED"
SortColumn = "F"

End Select

If ComboBox1 = "VIN NUMBER" Then
    Range("A4").Select

ElseIf ComboBox1 = "VEHICLE" Then
    Range("B4").Select

ElseIf ComboBox1 = "CUSTOMER" Then
    Range("C4").Select

ElseIf ComboBox1 = "YEAR" Then
    Range("D4").Select

ElseIf ComboBox1 = "HONDA NUMBER" Then
    Range("E4").Select

ElseIf ComboBox1 = "SUPPLIED" Then
    Range("F4").Select
End If

If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
End If

With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess

    End With
    
Select Case ComboBox1
Case "DATE"
SortColumn = "G"

End Select
If ComboBox1 = "DATE" Then
    Range("G4").Select
    
If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
End If

With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlDecending, Header:=xlGuess
    End With
    End If
    Unload Me
    End Sub

 

[MARK858]

First thing that I can see is you have spelt xlDecending wrong, it should be xlDescending

 

[ipbr21054]

Sorry didnt see that but type mismatch for this

I think its because of the column G but i didnt want to put F and the sort would go ahead thus all the dates in G not then with the correct info for columns A-F

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

 

[MARK858]

What are the values of x and SortColumn when you hover your mouse over them?

 

[ipbr21054]

Well i cant answer that as i dont know what x refers to.

Column G are just dates 01/12/2012 16/10/2020 etc etc

 

[MARK858]

I don't want to know what is in column G, I want to know what the variables x and SortColumn are showing in that line when the code errors. Hover your mouse over each variable in the line.

 

[ipbr21054]

Ok sorry

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

See attached screenshots

 

[MARK858]

So what did you have in Combobox1 when you ran the code?

 

[ipbr21054]

I selected DATE from my options in the Combobox then pressed the sort command button
The original code works fine but my edit pops up the error

 

[MARK858]

It is giving the error because the SortColumn variable is empty where as it should be getting the column letter based on the select case triggered by ComboBox1.
Are you sure that it is the word DATE that you have in the ComboBox1 with no leading/trailing spaces and all capitals when the code is run?