VB. If with multiple Or inside

Question

I want to put this If in a macro but it gives me an error all the time. I dont know if "Or" is used correctly or not.

 Dim SMAT As String
 SMAT = "blahblahblah"
    (...)  
 If Cells(h + 2, 24) <> SMAT Or SMBE Or SMES Or SMFR Or SMGB Or SMGR Or SMRO1 Or SMRO2 Or SMRO3 Or SMDE Then
        C(j) = Cells(h + 2, 5)

What is the code supposed to do? Do you want to check that Cells(h + 2, 24) is not equal to all of those SM* strings? If so, you'll need to repeat the test each time. If (Cells(h + 2, 24) <> SMAT) Or (Cells(h + 2, 24) <> SMBE) Or ... Parentheses help. — Cody Gray
– Cody Gray ♦, Commented Jan 19, 2016 at 14:29
I still think they should be AND's not OR's but of course using 'like' operator was rubbish. — Tom Sharpe
– Tom Sharpe, Commented Jan 19, 2016 at 15:18

SierraOscar · Accepted Answer · 2016-01-19 14:54:21Z

4

Use a Select Case block instead:

Select Case Cells(H + 2, 24).Value
    Case SMAT, SMBE, SMES, SMFR, SMGB, SMGR, SMR01, SMR02, SMR03, SMDE
    Case Else
        c(j) = Cells(H + 2, 5).Value
End Select

Or another way using Evaluate(), just for variety*:

varConditions = Array(SMAT, SMBE, SMES, SMFR, SMGB, SMGR, SMR01, SMR02, SMR03, SMDE)

If Evaluate("ISERROR(MATCH(" & Cells(H + 2, 24).Value & ",{" & _
    Join(varConditions, ",") & "},0))") Then
        c(j) = Cells(H + 2, 5).Value
End If

* This Evaluate method will work when the array contains numbers - if you are using strings you would have to wrap each string in additional quotation marks

edited Jan 19, 2016 at 14:54

answered Jan 19, 2016 at 14:39

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Darren Bartrup-Cook Over a year ago

Plus 1 for the SELECT CASE.

Pedro Lastra Over a year ago

the Select Case block does exactly the opposite haha thanks anyway for your help

SierraOscar Over a year ago

@PedroLastra sorry, missed a line - I've just changed it so that it fits your logic

Elbert Villarreal · Accepted Answer · 2016-01-19 14:40:28Z

2

Here is the correction

Dim SMAT As String
SMAT = "blahblahblah"
'(...)
If Cells(H + 2, 24) <> SMAT Or _
   Cells(H + 2, 24) <> SMBE Or _
   Cells(H + 2, 24) <> SMES Or _
   Cells(H + 2, 24) <> SMFR Or _
   Cells(H + 2, 24) <> SMGB Or _
   Cells(H + 2, 24) <> SMGR Or _
   Cells(H + 2, 24) <> SMRO1 Or _
   Cells(H + 2, 24) <> SMRO2 Or _
   Cells(H + 2, 24) <> SMRO3 Or _
   Cells(H + 2, 24) <> SMDE Then
    c(j) = Cells(H + 2, 5)
End If

Or Operator (Visual Basic)

The error is because you are trying to "talk" to VBA like a person do, but the or does not take the parameter of another or. You need to tell in every parameter of each or to tell the complete logical test

firstCheck = a > b Or b > c
firstCheck = Logical_test Or Logical_test

edited Jan 19, 2016 at 14:40

answered Jan 19, 2016 at 14:35

Elbert Villarreal

1,7261 gold badge11 silver badges23 bronze badges

2 Comments

Pedro Lastra Over a year ago

Done! Thanks Cody Gray and ElbertV

Tom Sharpe Over a year ago

IMHO the syntax is right but the logic is wrong. Sorry!

Collectives™ on Stack Overflow

VB. If with multiple Or inside

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related