asked on

Search multi-criteria in VBA Excel

Is there a better way to match a multi-criteria for a cell simliar ot the code below in Excel ?

Thx

If Cells(i, 1) Like "win*" or Cells(i, 1) Like "linux*" or Cells(i, 1) Like "Unix*" Then

Worksheets("Sheet2").Cells(i, 5) = Cells(i, 1)

Ryan Chong

if you want to compare the string case-insensitively, try this instead:

If InStr(1, v, "win", vbTextCompare) = 1 Or InStr(1, v, "linux", vbTextCompare) = 1 Or InStr(1, v, "unix", vbTextCompare) = 1 Then

Open in new window

or doing a loop with array will work too

Function Compare(ByVal v As String) As Boolean
    Dim ArrToCompare()
    ArrToCompare = Array("win", "linux", "unix")
   
    Dim i As Integer
   
    For i = 0 To UBound(ArrToCompare)
        If InStr(1, v, ArrToCompare(i), vbTextCompare) = 1 Then
            Compare = True
            Exit Function
        End If
    Next i
    Compare = False
End Function

Open in new window

then call it like:

If Compare(Cells(i, 1)) Then

Open in new window

Martin Liss

Or you can filter the data with this code which assumes the data starts in A2.

Sub ShowOS()
    Dim lngLastRow As Long
    
    With ActiveSheet
        lngLastRow = .UsedRange.Rows.Count
        .Columns("A:A").Select
        Selection.AutoFilter
        .Range("$A$2:$A$" & lngLastRow).AutoFilter Field:=1, Criteria1:="=Win*", _
            Operator:=xlOr
        .Range("$A$2:$A$" & lngLastRow).AutoFilter Field:=1, Criteria1:="=Lin*", _
            Operator:=xlOr, Criteria2:="=Lin*"
        .Range("$A$2:$A$" & lngLastRow).AutoFilter Field:=1, Criteria1:="=Uni*", _
            Operator:=xlOr, Criteria2:="=Lin*"
    End With
End Sub

Open in new window

Simon Leung

ASKER

MultiCriteria.xlsm

Seem like Window can be sort out successfully. Beside, how to read back the input result

Ryan Chong

Seem like Window can be sort out successfully. Beside, how to read back the input result

Can you elaborate further what's your requirement as well as the expected result?

Simon Leung

ASKER

Sorry, typo mistake. It can't sort out successfully.

Simon Leung

ASKER

After running the macro, two cells with "Window" are also filter out.

Open in new window

Simon Leung

ASKER

Ryan Chong

AutoFilter only can put up to 2 filters if not wrong.

so you got to try the Array method instead. I will share this later.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial