Sign in to follow this  
Followers 0
Coffeee

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

7 posts in this topic

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

I tried that before, and it will work fine with "Red", but as soon as I set it to "White" or "Mixed", it'll throw up the error message?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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..)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?" ?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.