• 0

[VBA] If cell contains this, this, or this, then...


Question

Figured this would be quite simple, but an hour or two searching Google has really scrambled my brain on this one.

In short, I have a drop down list in Excel, with 3 options: Red, White, Mixed. However I need to run a macro, on a button push that will check that 3 drop downs all contain either red, white, mixed... or blank.

So far I've managed:

    If Not (Range("C17").Value) = "Red" Or "White" Or "Mixed" Then
        MsgBox "Incorrect wine choice" & vbCrLf & _
        "Please re-enter the colour of wine"
        Exit Sub
    End If

This is how I would have thought it would work but.. apparently not. Running it with just "= "Red"" is fine however.

Much appreciated if someone could give us a hand,

Thanks,

C.

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

Subtle syntax mistake. You need:

If Not (Range("C17").Value) = "Red" Or (Range("C17").Value) = "White" Or (Range("C17").Value) ="Mixed" Then
        MsgBox "Incorrect wine choice" & vbCrLf & _
        "Please re-enter the colour of wine"
        Exit Sub
    End If

Link to comment
Share on other sites

  • 0

VBA has always been a bit odd.

Try

If Not ((Range("C17").Value = "Red") Or (Range("C17").Value = "White") Or (Range("C17").Value ="Mixed")) Then
        MsgBox "Incorrect wine choice" & vbCrLf & _
        "Please re-enter the colour of wine"
        Exit Sub
End If

You could also try:

Select Case Range("C17").Value
Case "Red"
Case "White"
Case "Mixed"
Case Else
MsgBox "Incorrect wine choice" & vbCrLf & _
        "Please re-enter the colour of wine"
Exit Sub
End Select

Link to comment
Share on other sites

  • 0

Spot on. Is it always the case then, if you wish to use 'Or' or 'And', you have to re-use the function or what not? (plus the use of brackets, it seems..)

Link to comment
Share on other sites

  • 0

Spot on. Is it always the case then, if you wish to use 'Or' or 'And', you have to re-use the function or what not? (plus the use of brackets, it seems..)

Yeah it was originally evaluating it as "If it isn't red, or it is white or mixed then fail". Gotta use a select case or parentheses.

Link to comment
Share on other sites

  • 0

Hi,

Firstly...

Select Case Range("C17").Value
Case "Red","White","Mixed"
Case Else
MsgBox "Incorrect wine choice" & vbCrLf & _
        "Please re-enter the colour of wine"
Exit Sub
End Select

Secondly isn't the "mixed" wine usually called "ros?" ?

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.