Jump to content



Photo

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


  • Please log in to reply
6 replies to this topic

#1 Coffeee

Coffeee

    Graham Stevens

  • Joined: 08-February 03
  • Location: West Midlands, UK.

Posted 10 October 2010 - 18:47

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.


#2 omnicdoer

omnicdoer

    ☕☕☕☕☕☕⚡⚡⚡⚡⚡

  • Joined: 18-February 08
  • Location: ☕☕☕☕⚡⚡⚡⚡⚡

Posted 10 October 2010 - 18:50

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


#3 OP Coffeee

Coffeee

    Graham Stevens

  • Joined: 08-February 03
  • Location: West Midlands, UK.

Posted 10 October 2010 - 18:58

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?

#4 omnicdoer

omnicdoer

    ☕☕☕☕☕☕⚡⚡⚡⚡⚡

  • Joined: 18-February 08
  • Location: ☕☕☕☕⚡⚡⚡⚡⚡

Posted 10 October 2010 - 19:01

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


#5 OP Coffeee

Coffeee

    Graham Stevens

  • Joined: 08-February 03
  • Location: West Midlands, UK.

Posted 10 October 2010 - 19:04

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

#6 omnicdoer

omnicdoer

    ☕☕☕☕☕☕⚡⚡⚡⚡⚡

  • Joined: 18-February 08
  • Location: ☕☕☕☕⚡⚡⚡⚡⚡

Posted 10 October 2010 - 19:07

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.

#7 Kami-

Kami-

    ♫ d(-_-)b ♫

  • Tech Issues Solved: 2
  • Joined: 28-July 08
  • Location: SandBox

Posted 12 October 2010 - 20:34

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



Click here to login or here to register to remove this ad, it's free!