Results 1 to 7 of 7

Thread: Long IF

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hoping to pick at the brains of loungers (again) , for a more elegant approach?

    [codebox]
    If w2 = 2 Or w2 = 14 Or w2 = 26 Or w2 = 38 Or w2 = 50 Then
    v.Sort Key1:=Range("N5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 3 Or w2 = 15 Or w2 = 27 Or w2 = 39 Or w2 = 51 Then
    v.Sort Key1:=Range("N5"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 4 Or w2 = 16 Or w2 = 28 Or w2 = 40 Or w2 = 52 Then
    v.Sort Key1:=Range("M5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 5 Or w2 = 17 Or w2 = 29 Or w2 = 41 Or w2 = 53 Then
    v.Sort Key1:=Range("M5"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 6 Or w2 = 18 Or w2 = 30 Or w2 = 42 Or w2 = 54 Then
    v.Sort Key1:=Range("O5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 7 Or w2 = 19 Or w2 = 31 Or w2 = 43 Or w2 = 55 Then
    v.Sort Key1:=Range("O5"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 8 Or w2 = 20 Or w2 = 32 Or w2 = 44 Or w2 = 56 Then
    v.Sort Key1:=Range("L5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 9 Or w2 = 21 Or w2 = 33 Or w2 = 45 Or w2 = 57 Then
    v.Sort Key1:=Range("I5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 10 Or w2 = 22 Or w2 = 34 Or w2 = 46 Or w2 = 58 Then
    v.Sort Key1:=Range("H5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 11 Or w2 = 23 Or w2 = 35 Or w2 = 47 Or w2 = 59 Then
    v.Sort Key1:=Range("H5"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 12 Or w2 = 24 Or w2 = 36 Or w2 = 48 Or w2 = 60 Then
    v.Sort Key1:=Range("K5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ElseIf w2 = 13 Or w2 = 25 Or w2 = 37 Or w2 = 49 Or w2 = 61 Then
    v.Sort Key1:=Range("J5"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End If
    [/codebox]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Code:
    	Select Case w2
    	Case 2, 14, 26, 38, 50
    		v.Sort Key1:=Range("N5"), Order1:=xlDescending, Header:=xlNo
    	Case 3, 15, 27, 39, 51
    		v.Sort Key1:=Range("N5"), Order1:=xlAscending, Header:=xlNo
    	Case 4, 16, 28, 40, 52
    		v.Sort Key1:=Range("M5"), Order1:=xlDescending, Header:=xlNo
    	Case 5, 17, 29, 41, 53
    		v.Sort Key1:=Range("M5"), Order1:=xlAscending, Header:=xlNo
    	Case 6, 18, 30, 42, 54
    		v.Sort Key1:=Range("O5"), Order1:=xlDescending, Header:=xlNo
    	Case 7, 19, 31, 43, 55
    		v.Sort Key1:=Range("O5"), Order1:=xlAscending, Header:=xlNo
    	Case 8, 20, 32, 44, 56
    		v.Sort Key1:=Range("L5"), Order1:=xlDescending, Header:=xlNo
    	Case 9, 21, 33, 45, 57
    		v.Sort Key1:=Range("I5"), Order1:=xlDescending, Header:=xlNo
    	Case 10, 22, 34, 46, 58
    	   v.Sort Key1:=Range("H5"), Order1:=xlDescending, Header:=xlNo
    	Case 11, 23, 35, 47, 59
    		v.Sort Key1:=Range("H5"), Order1:=xlAscending, Header:=xlNo
    	Case 12, 24, 36, 48, 60
    		v.Sort Key1:=Range("K5"), Order1:=xlDescending, Header:=xlNo
    	Case 13, 25, 37, 49, 61
    		v.Sort Key1:=Range("J5"), Order1:=xlDescending, Header:=xlNo
    	End Select

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Or slightly simpler:

    Code:
    	Select Case w2 Mod 12
    	Case 2
    		v.Sort Key1:=Range("N5"), Order1:=xlDescending, Header:=xlNo
    	Case 3
    		v.Sort Key1:=Range("N5"), Order1:=xlAscending, Header:=xlNo
    	Case 4
    		v.Sort Key1:=Range("M5"), Order1:=xlDescending, Header:=xlNo
    	Case 5
    		v.Sort Key1:=Range("M5"), Order1:=xlAscending, Header:=xlNo
    	Case 6
    		v.Sort Key1:=Range("O5"), Order1:=xlDescending, Header:=xlNo
    	Case 7
    		v.Sort Key1:=Range("O5"), Order1:=xlAscending, Header:=xlNo
    	Case 8
    		v.Sort Key1:=Range("L5"), Order1:=xlDescending, Header:=xlNo
    	Case 9
    		v.Sort Key1:=Range("I5"), Order1:=xlDescending, Header:=xlNo
    	Case 10
    	   v.Sort Key1:=Range("H5"), Order1:=xlDescending, Header:=xlNo
    	Case 11
    		v.Sort Key1:=Range("H5"), Order1:=xlAscending, Header:=xlNo
    	Case 0
    		v.Sort Key1:=Range("K5"), Order1:=xlDescending, Header:=xlNo
    	Case 1
    		v.Sort Key1:=Range("J5"), Order1:=xlDescending, Header:=xlNo
    	End Select

  4. #4

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Also note that I omitted several arguments because they had the default value.

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797028' date='08-Oct-2009 22:38']Also note that I omitted several arguments because they had the default value.[/quote]

    Am I correct in assuming then that Header default is xlYes ? So if I included the headers in my range, then the header.xlNo could also be ommitted?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    According to Sort Method, the default is xlNo, but I have found it better to always specify this argument to avoid confusion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •