Чтобы выполнить поиск по нескольким строкам в Excel VBA с помощью функции Instr, вы можете использовать несколько методов. Вот несколько примеров:

Метод 1: использование массива строк поиска

Sub MultipleStringSearch()
    Dim searchStrings() As Variant
    Dim searchString As Variant
    Dim cell As Range

    ' Define the search strings
    searchStrings = Array("apple", "banana", "orange")

    ' Loop through each cell in the range
    For Each cell In Range("A1:A10")
        ' Loop through each search string
        For Each searchString In searchStrings
            ' Check if the search string is found in the cell
            If InStr(1, cell.Value, searchString, vbTextCompare) > 0 Then
                ' Do something if the search string is found
                MsgBox "Found: " & searchString & " in cell " & cell.Address
            End If
        Next searchString
    Next cell
End Sub

Метод 2: использование строки поисковых строк

Sub MultipleStringSearch()
    Dim searchStrings As String
    Dim searchString As Variant
    Dim cell As Range

    ' Define the search strings
    searchStrings = "apple,banana,orange"

    ' Loop through each cell in the range
    For Each cell In Range("A1:A10")
        ' Loop through each search string
        For Each searchString In Split(searchStrings, ",")
            ' Check if the search string is found in the cell
            If InStr(1, cell.Value, searchString, vbTextCompare) > 0 Then
                ' Do something if the search string is found
                MsgBox "Found: " & searchString & " in cell " & cell.Address
            End If
        Next searchString
    Next cell
End Sub

Метод 3. Использование функции рабочего листа

Sub MultipleStringSearch()
    Dim searchStrings As String
    Dim searchString As Variant
    Dim cell As Range
    Dim result As Variant

    ' Define the search strings
    searchStrings = "apple,banana,orange"

    ' Loop through each cell in the range
    For Each cell In Range("A1:A10")
        ' Use the WorksheetFunction.Search to check if any search string is found in the cell
        result = Application.WorksheetFunction.Search(Split(searchStrings, ","), cell.Value, 1)

        ' Check if any match is found
        If Not IsError(result) Then
            ' Loop through the search strings
            For Each searchString In Split(searchStrings, ",")
                ' Check if the search string is found in the cell
                If InStr(1, cell.Value, searchString, vbTextCompare) > 0 Then
                    ' Do something if the search string is found
                    MsgBox "Found: " & searchString & " in cell " & cell.Address
                End If
            Next searchString
        End If
    Next cell
End Sub