CODEWITHSUNDEEP
vbaexcel
Pedro Lastra
Pedro Lastra

Reputation: 63

VB. If with multiple Or inside

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)

Upvotes: 2

Views: 70

Answers (2)

SierraOscar
SierraOscar

Reputation: 17647

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

Upvotes: 4

Elbert Villarreal
Elbert Villarreal

Reputation: 1716

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

Upvotes: 2

Related Questions