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

 

[ipbr21054]

Yes sure.
The code ive got is the same as supplied above and all capitals etc

 

[ipbr21054]

What ive just done is to copy the line of which is shown in yellow,i then put back the original code BUT used the line i just copied.

I ran the code & no errors so spelling etc is fine.

 

[ipbr21054]

It might be easier to use the original code & just edit for the column G to soert NEW - OLD as opposed trying to find this mistake ?

 

[MARK858]

The error has nothing to do with the sort being Ascending or Descending, it is because your variable isn't picking up a value.
Step through your code using F8 so you can see exactly what it is doing.

 

[ipbr21054]

Ive selected DATE in the combobox list & asked it to sort.
The F8 steps through Select Case options in the code where DATE isnt there.
It then steps through the combobox options where DATE also isnt there.
Steeping on through to the point its supposed to sort will not work as DATE wasnt selected in the first two parts above.
This then puts up the MSG box.

I was expecting the code to then jump to the lower part of the code to then see where DATE actually is situted

 

[MARK858]

Can you upload your workbook to a free file hosting site like www.box.com or www.dropbox.com.
Mark the file for sharing and then paste the link it provides in the thread.

Make sure that you amend/remove any sensitive data before uploading.

 

[Fluff]

If you put the filter part of the inside your if len then you should be ok

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
   
   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
End If
Select Case ComboBox1
Case "DATE"
SortColumn = "G"

End Select
If ComboBox1 = "DATE" Then
    Range("G4").Select
End If
If Len(SortColumn) <> 0 Then
   Application.ScreenUpdating = False
   
   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

 

[ipbr21054]

Here we go thanks

Workbook

 

[MARK858]

Have you tried fluff's suggestion?

 

[ipbr21054]

Yes but it then gives me the same message / error but this time selects in yellow the same code but the endone.