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]

What did you have in the ComboBox as it runs fine for me if DATE is in the ComboBox

 

[ipbr21054]

Here see screenshot

Why dont you post the code you have ??

 

[Fluff]

Try changing the blue code to

If ComboBox1 = "DATE" Then
    Range("G4").Select
    SortColumn = "G"
   Application.ScreenUpdating = False
   
   With ws
   If .AutoFilterMode Then .AutoFilterMode = False
   x = .Cells(.Rows.Count, 1).End(xlUp).Row
   .Range("G3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlDescending, Header:=xlGuess
       End With
End If
    Unload Me
    End Sub

Otherwise you may have problems if Date is not selected

 

[ipbr21054]

@Fluff i have tried that & the Date sorts as expected BUT columns A-F dont move with there respective dates.
Thus meaning the data is now broken as dates do not match the correct customer etc.

 

[MARK858]

Maybe..

If ComboBox1 = "DATE" Then
    Range("G4").Select
    SortColumn = "G"
   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
   .Range("G3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlDescending, Header:=xlGuess
       End With
End If
    Unload Me
    End Sub

 

[ipbr21054]

@MARK858 that also puts the ranges all out of sync.

Thanks both of you but im going to leave it and use the original as i cant afford to get this wrong.

Have a good night

 

[MARK858]

If you want all the columns to move with column G then we are back to what we had before only in the If statement

If ComboBox1 = "DATE" Then
    Range("G4").Select
    SortColumn = "G"
   Application.ScreenUpdating = False

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

If it is different then post a before and after sample so we know what you want.

 

[ipbr21054]

Thanks but I’m going to leave it as if some error happens and I don’t catch it it will mess me up I. The future but thanks.
Basically I need to keep the customer in column A matched with therecords in columns B,C,D,E,F,G

What I found was example Tom Jones date was say 01/01/2001 but when column G was sorted Tom Jones date then become out of sync as it showed 16-12/2017 etc