ElseIf - multiple ranges using Or statement not working

[sycodiz]

I am using Elseif because I have a lot of stuff my code is checking. I am stuck on one area. I need to make sure what is in cell E23 shows up in one of the other cells in my code.
Sub ReviewItems()

If Range("C5").Value <> "New Item or Expand" Then

'There is a lot of ElseIfs before this statement I am having issues with

ElseIf (Range("E23").Value <> Range("C55").Value Or Range("E23").Value <> Range("E55").Value Or _
Range("E23").Value <> Range("G55").Value Or Range("E23").Value <> Range("C56").Value Or _
Range("E23").Value <> Range("E56").Value Or Range("E23").Value <> Range("G56").Value) Then
'ElseIf Range("E23").Value <> Range("C55").Value Then
MsgBox "UPC on Basic Record is not found in Item Control Ordering"

Else
MsgBox "Passed validations"
End If

The problem is - it just does not work. However the line that I comment blocked out (and formatted red above) does work correctly.
Any suggestions? Am i missing something?

 

[StephenCrump]

Can you please explain in more detail what you mean by "does not work"?

As the code is written above:

- It is not doing anything if the long ElseIf statement is True
- Code execution can not get to the MsgBox statement.

If Range("C5").Value <> "New Item or Expand" Then
    '...

ElseIf (Range("E23").Value <> Range("C55").Value Or Range("E23").Value <> Range("E55").Value Or _
    Range("E23").Value <> Range("G55").Value Or Range("E23").Value <> Range("C56").Value Or _
    Range("E23").Value <> Range("E56").Value Or Range("E23").Value <> Range("G56").Value) Then
    'Execution gets here only if ALL previous ElseIf statements are NOT satisfied
    '... What should the code do here?
ElseIf Range("E23").Value <> Range("C55").Value Then
    'Execution can NOT get here.  If E23<>C55, then the previous ElseIf condition will have been satisfied
    MsgBox "UPC on Basic Record is not found in Item Control Ordering"
Else
    MsgBox "Passed validations"
End If

 

[sycodiz]

Sorry for taking so long to reply back.
First when you posted the code again that I wrote, you removed the comment block I had on
'ElseIf Range("E23").Value <> Range("C55").Value Then

I know that code works correctly which is why I had it commented out.
I am looking to see if the number that is in cell E23 is in any of the following cells (C55, E55, G55, C56, E56, or G56). If it is there ok to move on, if not then I need a message stating number is missing.

 

[rlv01]

How about something like this?

    Dim FoundNumber As Boolean
    Dim TestRange As Range, R As Range
    Dim TestValue As Variant
    
    Set TestRange = Application.Union(Range("C55:C56"), Range("E55:E56"), Range("G55:G56"))
    TestValue = Range("E23").Value
    
    For Each R In TestRange
        If R.Value = TestValue Then
            FoundNumber = True
            Exit For
        End If
    Next R
    
    If Not FoundNumber Then
        MsgBox "UPC on Basic Record is not found in Item Control Ordering", vbOKOnly Or vbExclamation, Application.Name
        Exit Sub
    Else
        MsgBox "Passed validations", vbOKOnly Or vbExclamation, Application.Name
    End If

 

[CubaRJ]

Hi, I liked rlv01 approach, but you can also accomplish this by making a "Select Case" instead of using "If".

The benefit of Select Case is that you can control and apply what do in every case found, for example to apply validations to cells or userforms. IMHO, it is also cleaner and easy to understand it later.

Here you can find some nice info: VBA Select Case - A Complete Guide - Excel Macro Mastery

Hope it helps.

 

[sycodiz]

rvl01 thanks that worked. CubaRJ, I am still trying to learn Select Case