Sort code advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,460
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
4,247
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
14,460
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
4,247
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
14,460
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
4,247
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: 5
  • 525.jpg
    525.jpg
    43.6 KB · Views: 5
  • 526.jpg
    526.jpg
    43 KB · Views: 6
  • 527.jpg
    527.jpg
    41 KB · Views: 5
  • 528.jpg
    528.jpg
    35.4 KB · Views: 5

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,460
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
4,247
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
14,460
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,200
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top