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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

KlausW
  • Solved
Excel VBA Error - Unable to get the Insert property of the Picture Class
Replies
7
Views
486
KlausW
KlausW
R
  • Solved
Macro To Move Files - Will Not Move .msg or .csv files
Replies
4
Views
145
rachel06
R
D
  • Solved
Error 1004
2
Replies
18
Views
475
dmt32
D
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
325
chazrab
C
M
  • Question Question
Split Excel Workbook to separate .xlsx files for each unique value in Column A, also copy worksheet for Drop Down reference
Replies
9
Views
518
Alex Blakenburg
A

Forum statistics

Threads
1,225,481
Messages
6,185,247
Members
453,283
Latest member
Shortm88

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