Sort code advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. 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
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,322
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
First thing that I can see is you have spelt xlDecending wrong, it should be xlDescending
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows
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

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,322
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What are the values of x and SortColumn when you hover your mouse over them?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,322
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok sorry

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

See attached screenshots
 

Attachments

  • 524.jpg
    524.jpg
    27.9 KB · Views: 4
  • 525.jpg
    525.jpg
    43.6 KB · Views: 4
  • 526.jpg
    526.jpg
    43 KB · Views: 6
  • 527.jpg
    527.jpg
    41 KB · Views: 4
  • 528.jpg
    528.jpg
    35.4 KB · Views: 4

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,322
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
So what did you have in Combobox1 when you ran the code?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,322
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,535
Messages
5,523,419
Members
409,520
Latest member
HarryP96

This Week's Hot Topics

Top