CODEWITHSUNDEEP
vbaexcel
Ashish Baboo
Ashish Baboo

Reputation: 154

How to write an OR statement in VBA having large number of conditions

i am working on a code where i am writing an IF statement. The code goes some thing like this.

If Cells(rw, col3).Value = "11312" Or Cells(rw, col3).Value = "21345"Or _
Cells(rw, col3).Value = "176" Or Cells(rw, col3).Value = "134" then
Else
Cells(rw, LC + 2).Value = "WRONG OCC code"
Endif

The problem is, i have around 2000 of these OR conditions. When i am trying to put all conditions, VBA is saying too complex code or too much continuation statements. Please help me solve this.

Thannk you in Advance.

Upvotes: 0

Views: 375

Answers (2)

Tom
Tom

Reputation: 9878

Put all of your values into a 1D array (choosing however you wish to populate the array) and then test whether they're in the array using the following function:

Option Explicit
Public Sub test()
    Dim arr() As Variant

    With ThisWorkbook.Sheets("AD")
        arr = Application.Transpose(.Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)).Value2)
    End With
    If IsInArray(Cells(rw, col3).Value, arr) Then

    Else
        Cells(rw, LC + 2).Value = "WRONG OCC code"
    End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Or simply this...

arr = Array("11312", "21345", "176", "134")
If Not IsError(Application.Match(CStr(Cells(rw, col3)), arr, 0)) Then
    MsgBox "Found!"
Else
    Cells(rw, LC + 2).Value = "WRONG OCC code"
End If

Edit: If you have a long list of criteria, better list all of them into a Sheet and then read them into an Array.

The following code assumes that you have a sheet called "Criteria" and all the criteria are listed in column A starting from row1.

Then you can try the below code to produce the same result...

Dim arr
arr = Sheets("Criteria").Range("A1").CurrentRegion.Value
If Not IsError(Application.Match(Cells(rw, col3), arr, 0)) Then
    MsgBox "Found!"
Else
    Cells(rw, LC + 2).Value = "WRONG OCC code"
End If

Upvotes: 1

Related Questions