ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,271
- Office Version
- 2007
- Platform
- Windows
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
I would like for the DATE to sort NEW - OLD
Ive tried to edit the above but running into a few run time errors.
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
Rich (BB code):
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.
Rich (BB code):
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