VBA - check if a string is is 1 of those in a column of a different sheet, in an if statement

Question

Hello i want to simpify the formula from

If InStr(1, Sheets("Le 2250").Cells(i, 1).Value, "250-") Or _
If InStr(1, Sheets("Le 2250").Cells(i, 1).Value, "135-") Or _
If InStr(1, Sheets("Le 2250").Cells(i, 1).Value, "700-")

to have the "250-" be 1 of the values in a column of a specific sheet, rather than having to put many "Or if ()" functions with the numerous strings i have to lpok for

Any help appreciated.

Consider building a [regular expression](https://stackoverflow.com/q/22542834/4088852) and using that to test the cell. — Comintern, Aug 06 '18 at 23:54

score 0 · Answer 1 · answered Aug 07 '18 at 00:19

Basically, build an array of your test values, and loop that array until you find something.

Something like this

Sub Demo()
    Dim ws As Worksheet
    Dim rTestStings As Range, TestStings As Variant
    Dim TestValue As Variant
    Dim idx As Long
    Dim Found As Boolean

    'Get Test Strings from Sheet.  Adjust to suit your data
    With rTestStings = Worksheets("specific sheet")
        Set rTestStings = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    TestStings = rTestStings.Value2

    Set ws = Sheets("Le 2250")

    'I'm guessing you are doing something like this
    For i = SomeValue To SomeOtherValue
        TestValue = ws.Cells(i, 1).Value

        Found = False
        For idx = LBound(TestStings, 1) To UBound(TestStings, 1)
            If Not IsEmpty(TestStings(idx, 1)) Then  'incase there are gaps in your test data
                If InStr(TestValue, TestStings(idx, 1)) Then
                    Found = True
                    Exit For
                End If
            End If
        Next

        If Found Then
            MsgBox "Found " & TestStings(idx, 1) & " in cell " & ws.Cells(i, 1).Address
            ' do something ...

        End If
    Next i
End Sub

score 0 · Accepted Answer · answered Aug 07 '18 at 00:57

Here is an alternative that uses the Evaluate method...

If Evaluate("OR(ISNUMBER(MATCH({""*250-*"",""*135-*"",""*700-*""},{""" & Sheets("Le 2250").Cells(i, 1).Value & """},0)))") Then

Note, however, the number of characters used with the Evaluate method cannot exceed 255, otherwise an error will be returned.

VBA - check if a string is is 1 of those in a column of a different sheet, in an if statement

2 Answers2