Better way of writing the codesv

L

LFKim2018

Active Member
Joined
Mar 24, 2018
Messages
267
what could be a better way of writing these codes.
they worked but looked messy.
many many thanks


Code: 
Private Sub CommandButton1_Click()
    x = Val(InputBox("(1) = A  (2) = B  (3) = C  (4) = D  (5) = E", "Select Column to sort (1~5).."))
    If x = 0 Then
       Exit Sub
    End If
    If x < 1 Or x > 5 Then
        MsgBox "Enter 1 ~ 5 only..", vbCritical
        Exit Sub
    End If
    If x = 1 Then
        Call SortCol("A", "A1:E", "A1:A")
    End If
    If x = 2 Then
        Call SortCol("B", "A1:E", "B1:B")
    End If
    If x = 3 Then
        Call SortCol("C", "A1:E", "C1:C")
    End If
    If x = 4 Then
        Call SortCol("D", "A1:E", "D1:D")
    End If
    If x = 5 Then
        Call SortCol("E", "A1:E", "E1:E")
    End If
    Range("A2").Select
End Sub


Sub SortCol(mcol As String, mrng As String, mrng1 As String)
    lastrow = Cells(Rows.Count, mcol).End(xlUp).Row
    Range(mrng & lastrow).Select
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range(mrng1 & lastrow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").sort
        .SetRange Range(mrng & lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
LFKim2018,

You might try using the IF / Then / ElseIf construct; not only will it be neater but also execute slightly faster.

Or you might explore the Select Case statement, also neat and efficient.

Cheers,

tonyyy
 
Upvote 0
tonyyy said:
LFKim2018,

You might try using the IF / Then / ElseIf construct; not only will it be neater but also execute slightly faster.

Or you might explore the Select Case statement, also neat and efficient.

Cheers,

tonyyy
Click to expand...

Mr. tonyyy
Thank you for the fast reply.
could you kindly write at least the structure (I will just fill in the codes)
many many thanks
 
Upvote 0
LFKim2018 said:
Mr. tonyyy
Thank you for the fast reply.
could you kindly write at least the structure (I will just fill in the codes)
many many thanks
Click to expand...

the if elseif - I got it.
can't do the select case statement. (such a NOOB..) :(
kindly provide the structure for this and I will fill in the codes
many many thanks
 
Upvote 0
Code: 
Private Sub CommandButton1_Click()
x = Val(InputBox("(1) = A  (2) = B  (3) = C  (4) = D  (5) = E", "Select Column to sort (1~5).."))
Select Case x
    Case 0
        Exit Sub
    Case Is < 1
        MsgBox "Enter 1 ~ 5 only..", vbCritical
        Exit Sub
    Case Is > 5
        MsgBox "Enter 1 ~ 5 only..", vbCritical
        Exit Sub
    Case 1: Call SortCol("A", "A1:E", "A1:A")
    Case 2: Call SortCol("B", "A1:E", "B1:B")
    Case 3: Call SortCol("C", "A1:E", "C1:C")
    Case 4: Call SortCol("D", "A1:E", "D1:D")
    Case 5: Call SortCol("E", "A1:E", "E1:E")
End Select
Range("A2").Select
End Sub

How to use the Select Case statement
 
Upvote 0
LFKim2018 said:
what could be a better way of writing these codes.
they worked but looked messy.
many many thanks


Code: 
Private Sub CommandButton1_Click()
    x = Val(InputBox("(1) = A  (2) = B  (3) = C  (4) = D  (5) = E", "Select Column to sort (1~5).."))
    If x = 0 Then
       Exit Sub
    End If
    If x < 1 Or x > 5 Then
        MsgBox "Enter 1 ~ 5 only..", vbCritical
        Exit Sub
    End If
[B][COLOR="#FF0000"]    If x = 1 Then
        Call SortCol("A", "A1:E", "A1:A")
    End If
    If x = 2 Then
        Call SortCol("B", "A1:E", "B1:B")
    End If
    If x = 3 Then
        Call SortCol("C", "A1:E", "C1:C")
    End If
    If x = 4 Then
        Call SortCol("D", "A1:E", "D1:D")
    End If
    If x = 5 Then
        Call SortCol("E", "A1:E", "E1:E")
    End If[/COLOR][/B]
    Range("A2").Select
End Sub
[/QUOTE]
You should be able to replace everything I highlighted in red above with this single line of code...
[CODE][table="width: 500"]
[tr]
	[td]Call SortCol(Chr(64 + X), "A1:E", Left(Cells(1, X).Resize(2).Address(0, 0), 4))[/td]
[/tr]
[/table]
Click to expand...
 
Last edited:
Upvote 0
tonyyy said:
Code: 
Private Sub CommandButton1_Click()
x = Val(InputBox("(1) = A  (2) = B  (3) = C  (4) = D  (5) = E", "Select Column to sort (1~5).."))
Select Case x
    Case 0
        Exit Sub
    Case Is < 1
        MsgBox "Enter 1 ~ 5 only..", vbCritical
        Exit Sub
    Case Is > 5
        MsgBox "Enter 1 ~ 5 only..", vbCritical
        Exit Sub
    Case 1: Call SortCol("A", "A1:E", "A1:A")
    Case 2: Call SortCol("B", "A1:E", "B1:B")
    Case 3: Call SortCol("C", "A1:E", "C1:C")
    Case 4: Call SortCol("D", "A1:E", "D1:D")
    Case 5: Call SortCol("E", "A1:E", "E1:E")
End Select
Range("A2").Select
End Sub

How to use the Select Case statement
Click to expand...

Mr. tonyyy
THANK YOU VERY VERY MUCH..
 
Upvote 0
LFKim2018 said:
Mr. tonyyy
THANK YOU VERY VERY MUCH..
Click to expand...
If you don't want to use the one-liner replacement I posted in Message #6 , I think the Select Case portion of the code that tonyyy posted can be tidied up a little more by writing it this way...
Code: 
  Select Case X
    Case 0: Exit Sub
    Case 1: Call SortCol("A", "A1:E", "A1:A")
    Case 2: Call SortCol("B", "A1:E", "B1:B")
    Case 3: Call SortCol("C", "A1:E", "C1:C")
    Case 4: Call SortCol("D", "A1:E", "D1:D")
    Case 5: Call SortCol("E", "A1:E", "E1:E")
    Case Else
      MsgBox "Enter 1 ~ 5 only..", vbCritical
      Exit Sub
  End Select
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA code not working on Mac
Replies
7
Views
561
Jim Gordon
J
P
  • Question
VBA Table Dynamically Sort Data and append Date Dynamically on new record Entered
Replies
1
Views
489
PeeBee
P
C
  • Question
Excel Vba attaching a file from SharePoint
Replies
0
Views
286
classmaz
C
C
  • Question
Using the Find method to copy 4 cell values above Find = X to another sheet for every value of X found in a column
Replies
6
Views
350
chazrab
C
U
  • Solved
VBA question involving 2 different workbooks
Replies
2
Views
406
ummjay
U

Forum statistics

Threads
1,214,923
Messages
6,122,283
Members
449,075
Latest member
staticfluids

Share this page

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
Back
Top