Variable Command Statment w/ Select Case

Originally Posted by mpeplow

What I want to do is use a select case statement within a statement, for example.

I want to make this code

Sub Sort_by(First_Sort As String, Optional Second_Sort As String)

If Second_Sort = "" Then
	Select Case First_Sort
	    Case A
	        Cells.Select    'Sort by Sku
	        Selection.Sort Key1:=Range("A" & "2"), Order1:=xlAscending, Header:=xlYes, _
            	OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
	            DataOption1:=xlSortTextAsNumbers

	    Case B
	        Cells.Select 'Sort by Core/Partner
	        Selection.Sort Key1:=Range("B"& "2"), Order1:=xlAscending, Header:=xlYes, _
	            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            	DataOption1:=xlSortTextAsNumbers    
	End Select
End If

End Sub

Work Like the code below, having a embedded select case statement within a statement so to cut down on the amount of code that I need to write.

Or does anyone know of a simple way to do this?

Sub Sort_by(First_Sort As String, Optional Second_Sort As String)

If Second_Sort = "" Then
	Select Case First_Sort
	    Case "A"
	        Cells.Select  
	        Selection.Sort Key1:=Range("A" & "2"), Order1:=xlAscending, _
		Select Case Second_String
			Case A
				 Key2:=Range("A2") _, Order2:=xlAscending, _
			Case B
				 Key2:=Range("B2") _, Order2:=xlAscending, _
		End Select
		 Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
	             DataOption1:=xlSortTextAsNumbers

	    Case "B""
	        Cells.Select 'Sort by Core/Partner
	        Selection.Sort Key1:=Range("B" & "2"), Order1:=xlAscending, Header:=xlYes, _
	            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            	DataOption1:=xlSortTextAsNumbers    
	End Select
End If

End Sub

Does that make sense?

Hi,

if I have your question correctly understood, then

Sub TestCase()
Dim a As String, b As String
a = "b"
b = "a"
Select Case a
Case "a"
    Select Case b
    Case "a"
        MsgBox "a & a"
    Case "b"
        MsgBox "a & b"
    Case Else
        MsgBox "a & other"
    End Select
Case "b"
    MsgBox "b"
End Select
End Sub

will allow you to set a & b to a or b to test that it works.

hth
---