Select Case Loop with multiple criteria

[bwm21]

I am building a macro to evaluate multiple (only 3 for now) conditions through a range of data. Here is what I have so far:

Sub Trial2()

Dim myCell As Range

For Each myCell In Range("Q2:S500")
    With myCell.Offset(0, -15)
        Select Case myCell.Value
            Case Is = "Production", "In-Scope", "Deployed"
                .Value = "Qualified"
            Case Else
                .Value = "Not Qualified"
        End Select
    End With
Next myCell

End Sub

My question is how do I create a Case to evaluate these criteria individually.
Example: The object being evaluated is "Production", "In-Scope", but is anything other than "Deployed"

 

[JEC]

Not sure about your needs but

Sub Trial2()

Dim myCell As Range

For Each myCell In Range("Q2:S500")
    With myCell.Offset(0, -15)
        Select Case myCell.Value
          Case "Production", "In-Scope": .Value = "Qualified"
          Case "Deployed": .Value = "Qualified"
          Case Else: .Value = "Not Qualified"
        End Select
    End With
Next myCell
End Sub

 

[bwm21]

I see what you are thinking. Let me try to phrase my need better:

Columns Q-R will be evaluated in each row. The goal will be to evaluate based on different combinations of those 3 criteria for the resulting .Value statement. If all 3 of those criteria are met, then it will be qualified.

If for Example Q is "Production", R is "In-Scope", but S is anything other than "Deployed", the .Value will be "Not Deployed".
However
If R is "In-Scope", S is "Deployed", but Q is anything other than "Production", the .Value will be "Non-Production".

 

[JEC]

Are you ok with a formula?

=CHOOSE(MAX(IF(ISERROR(MATCH({"Production","In-Scope,"Deployed"},Q2:S2,0)),{2,3,4},1)),"Qualified","Non-Production","Not-In-Scope","Not-Deployed")

 

[Joe4]

I see what you are thinking. Let me try to phrase my need better:

Columns Q-R will be evaluated in each row. The goal will be to evaluate based on different combinations of those 3 criteria for the resulting .Value statement. If all 3 of those criteria are met, then it will be qualified.

If for Example Q is "Production", R is "In-Scope", but S is anything other than "Deployed", the .Value will be "Not Deployed".
However
If R is "In-Scope", S is "Deployed", but Q is anything other than "Production", the .Value will be "Non-Production".

I would probably use IF THEN formulas where you can also make use of AND instead of SELECT CASE in this situation.

 

[JEC]

Formula converted to VBA

Sub jec()
 For Each myCell In Range("Q2:Q500")
   With myCell
    .Offset(0, -15) = Evaluate(Replace("choose(max(if(iserror(match({""Production"",""In-Scope"",""Deployed""},$$,0)),{2,3,4},1)),""Qualified"",""Non-Production"",""Not-In-Scope"",""Not-Deployed"")", "$$", .Resize(, 3).Address))
   End With
 Next
End Sub