"Select Case True" - how to include more than 3 "Case" statements?

Question

Can someone please help me with my code? I have introduced more than 3 "Case" statements (i.e. 6) and it seems that the last 2 "Cases" are not being picked-up (marked in the code below). Thanks

Sub yyy()

Dim TW As Workbook: Dim ac As Worksheet
Set TW = ThisWorkbook: Set ac = TW.Sheets(1)

Select Case True

Case ac.Range("G8") = "No" And ac.Range("G9") = "" And ac.Range("G10") = "" And ac.Range("G12") = "" And ac.Range("G11") = ""
ac.Range("F14") = "Continue with Test."

Case ac.Range("G8") = "Yes" And ac.Range("G9") = "" And ac.Range("G10") = "" And ac.Range("G12") = "" And ac.Range("G11") = ""
ac.Range("F14") = "No further action required."

'Yes, Yes, No, Yes
Case ac.Range("G8") <> "" And ac.Range("G9") = "Yes" And ac.Range("G10") = "Yes" And ac.Range("G12") = "Yes" And ac.Range("G11") = "No"
ac.Range("F14") = "No further action required."

'No,No,Yes,No
Case ac.Range("G8") = "No" And ac.Range("G9") = "No" And ac.Range("G10") = "No" And ac.Range("G12") = "No" And ac.Range("G11") = "Yes"
ac.Range("F14") = "Full Test."

'The following is not being picked up? I guess I have introduced too many "Case" statements?
'Cases for Yes No in Q4

Case ac.Range("G8") <> "No" And ac.Range("G9") <> "" And ac.Range("G10") <> "" And ac.Range("G12") <> "" And (ac.Range("G11") = "Yes" Or ac.Range("G11") = "No")
ac.Range("F14") = "Full Test."

Case ac.Range("G8") = "No" And ac.Range("G9") <> "" And ac.Range("G10") <> "" And ac.Range("G12") <> "" And ac.Range("G11") = "Yes"
ac.Range("F14") = "Full Test."

Case Else

End Select

End Sub

If there is a limit for the number of `Case`s, I am not aware of it, and it is certainly bigger than 3. If some of your cases are never picked up, either the conditions never match, or there is a `Case` that matches earlier. — GSerg, Aug 03 '19 at 22:46
It works up until the last 2 cases. I have tried to use "If" statements instead of Case (for the last 2) but it didn't change at all. Perplexed — Alien_Explorer, Aug 03 '19 at 22:54
You are using it wrong - you don't use `Select Case True` - you use `Select Case` and then a variable, and each `Case` is an option for the value of the variable. — braX, Aug 03 '19 at 23:24
Have you stepped through the code in the debugger to see why it's not acting as you intend? VBA very definitely supports more than 3 branches in a CASE, so if your last two are not working then either you have nothing that matches them or they've been matched by one of the previous tests. We don't have your data to test them, but you do and can step through in the debugger to see what's happening. — Ken White, Aug 04 '19 at 04:02
Yes, it is just not the intended usage and can cause some odd problems like this for beginners. — braX, Aug 04 '19 at 04:36
Put your yes no combinations into a separate variable and then each combination is then used in your cases , or just use some if and else if statement which might be easier to read — JimmyShoe, Aug 04 '19 at 07:13
@braX `Select Case True` [is fine](https://stackoverflow.com/q/794036/11683). — GSerg, Aug 04 '19 at 07:33
@JimmyShoe I think that rings a bell. I've had a similar issue in the past - I'll try to rearrange the logic and see what happens. I will update the board. — Alien_Explorer, Aug 04 '19 at 08:26
@braX If you are talking about not the intended usage, then it would be better to provide a link to some manual, which explicitly limits the usage to `Select Case `. — omegastripes, Aug 04 '19 at 13:00

score 1 · Accepted Answer · answered Aug 08 '19 at 07:08

It's all about the logic and the order of triggers. Please try below:

    Sub yyy()

    Dim TW As Workbook: Dim ac As Worksheet
    Set TW = ThisWorkbook: Set ac = TW.Sheets(1)

    Select Case True
    Case ac.Range("G8") = "No" And ac.Range("G9") = "No" And ac.Range("G10") = "No" And ac.Range("G12") = "No" And ac.Range("G11") = "Yes"
    ac.Range("F14") = "Full Test."
    Case Else
    End Select

    Select Case True
    Case ac.Range("G8") = "No" And ac.Range("G9") = "Yes" And ac.Range("G10") = "Yes" And ac.Range("G12") = "Yes" And ac.Range("G11") = "No"
    ac.Range("F14") = "No further action required."
    Case Else
    ac.Range("F14") = "Full Test."

    Select Case True
    Case ac.Range("G9") = "No" And ac.Range("G10") = "No" And ac.Range("G12") = "Yes" And ac.Range("G11") = "No"
    ac.Range("F14") = "No further action required."
    End Select

    Select Case True
    Case ac.Range("G11") = "Yes" Or ac.Range("G12") = "No"
    ac.Range("F14") = "Full CRA Test."
    End Select

    Select Case True
    Case ac.Range("G8") = "No" And (ac.Range("G9") = "Yes" Or ac.Range("G9") = "") And ac.Range("G10") = "" And ac.Range("G12") = "" And ac.Range("G11") = ""
    ac.Range("F14") = "Continue with Test."

    End Select
    End Select
    End Sub

"Select Case True" - how to include more than 3 "Case" statements?

1 Answers1