Passing an answer to a Select Case structure

[bgrice]

Hi. I'm getting confused trying to use Select Case structures and I'd be grateful for some help, please. My code is below for reference but I'll explain what I'm trying to achieve.
The purpose of the code is to display a message for the first select case statement that is found to be true. The code appears to work fine until it reaches the fourth select case structure and I assume I will have the same problem with statements five and six.
I was not able to embed a For Each statement in a Select Case structure and so my idea was calculate the outcome of For Each statements first and then to pass those answers to the select case structure - you might be able to see that I've tried to ascertain if 'a' is true in the first For Each statement and then I'm wanting to pass that to the fourth Select Case structure; and I'm trying to do similar things with 'b' and 'c' and select case five and six, respectively. (I did originally try this with ElseIf but ran into a similar problem in that I could not embed For Each into ElseIf e.g. ElseIf For Each ....
In my mind what I have tried to do is logical but clearly it is not - I am a real novice. Any help, or direction on alternative approaches, would be much appreciated. Thanks.

Sub CaseError()
Dim Msg As String
Dim Rng1, Rng2, Rng3 As Range
Dim PriceCell, ChangeCell, YearCell As Range
Dim a, b, c As Boolean
Set Rng1 = Range("A16:A500")
Set Rng2 = Range("J16:J500")
Set Rng3 = Range("L16:L500")

For Each PriceCell In Rng1
    If PriceCell.Value = True And PriceCell.Offset(0, 7).Value = False Then
        a = True
        Exit For
    End If
Next PriceCell

For Each ChangeCell In Rng2
    If ChangeCell.Value > Range("G13").Value Then
        b = True
        Exit For
    End If
Next ChangeCell

For Each YearCell In Rng3
    If YearCell.Value = 0 Then
        c = True
        Exit For
    End If
Next YearCell

    Select Case IsEmpty(Range("C3"))
        Case True
            Msg = "No manufacturer selected"
    End Select
    Select Case IsEmpty(Range("C4"))
        Case True
            Msg = "No Proposed implementation date selected"
    End Select
    Select Case WorksheetFunction.CountA(Range("A16:G500")) = 0
        Case True
            Msg = "No products selected"
    End Select
    Select Case a
        Case True
            Msg = "Proposed price for selected product(s) missing"
    End Select
    Select Case b
        Case True
            Msg = "Proposed price more than permitted threshold"
    End Select
    Select Case c
        Case True
            Msg = "Proposed implementation date too early"
    End Select
    
    MsgBox Msg
    
End Sub

 

[RoryA]

You can nest For Each structures inside other structures, so I'm not really sure why you had issues with it. I don't really see why you are using Select Case here rather than If...ElseIf but you can do it like this:

Select Case True
    Case IsEmpty(Range("C3"))
            Msg = "No manufacturer selected"
    Case IsEmpty(Range("C4"))
        Msg = "No Proposed implementation date selected"
     Case WorksheetFunction.CountA(Range("A16:G500")) = 0
        Msg = "No products selected"
     Case a
     Msg = "Proposed price for selected product(s) missing"
    Case b
        Msg = "Proposed price more than permitted threshold"
    Case c
        Msg = "Proposed implementation date too early"
    End Select

 

[bgrice]

Rory, thank you. This works. You are a star.
As I mentioned, I did initially try If...ElseIf but it wasn't having it. And VBA for dummies says that if more than three options then use select case, but I'm still new to this and learning. Thanks again.