How to find more than one word in Excel?

[man]

Hello

For example, I want to find cells that contains these 2 words [disposable] [bottles] in any order or with any other words in between, the search result shown will be A4 cell and A5 cell. How to do it? (Unable to use Ctrl+F because Ctrl+F only can search for 1 word)

My A1 cell to A6 cell contents are below
A1 cell: A water bottle is a container that is used to hold liquids, mainly water, for the purpose of transporting a drink while travelling or while otherwise away from a supply of potable water.
A2 cell: Water bottles are usually made of plastic, glass, metal, or some combination of those substances.
A3 cell: in the past, water bottles were sometimes made of wood, bark, or animal skins such as leather, hide and sheepskin.
A4 cell: Water bottles can be either disposable or reusable.
A5 cell: Disposable water bottles are often sold filled with potable water, while reusable bottles are often sold empty.
A6 cell: Reusable water bottles help cut down on consumer plastic waste and carbon emissions.

I am using Excel 2021

Thanks

 

[Cubist]

When you hit debug, hover your mouse over ws.Name on this line. What does it say? Is it one of the sheets in your workbook? Can't see your workbook so don't know what you're doing wrong. Post the screen shot of your code when it's yellow.

  If ws.Name <> "Output" Then

It doesn't matter what sheet you changed it from as long as there's a sheet called Output.

 

[man]

Is one of your sheets empty? If it is then there's nothing to find. This should handle when that happens.

Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
 
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
 
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 1)
 
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           k = k + 1
                        End If
                        On Error GoTo 0
                    Next j
                Next i
            End If
        End If
    Next ws
 
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 1).Value = b
    End If
End Sub

I close the excel sheet then reopen the excel sheet and redo everything and it is working fine now. No error. Output sheet can show the results. Thanks.

How to know each cell inside Output sheet is copied from what sheet?

How to save everything, including the VB editor and the Output sheet? This screenshot appear after I click save button. Even if I close the VB editor (Alt + F11. This will bring up the VB editor.) and delete the Output sheet, this screenshot will also appear after I click save button.

 

[Cubist]

You need to Save As a macro-enabled or binary workbook since you're using VBA now. This will output the sheet name in column B.

Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
   
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
   
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 2)
    
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           b(k, 2) = ws.Name
                           k = k + 1
                        End If
                        On Error GoTo 0 ' Reset error handling
                    Next j
                Next i
            End If
        End If
    Next ws
   
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 2).Value = b
    End If
End Sub

 

[man]

You need to Save As a macro-enabled or binary workbook since you're using VBA now. This will output the sheet name in column B.

Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
  
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
  
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 2)
   
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           b(k, 2) = ws.Name
                           k = k + 1
                        End If
                        On Error GoTo 0 ' Reset error handling
                    Next j
                Next i
            End If
        End If
    Next ws
  
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 2).Value = b
    End If
End Sub

The code works, can see sheet name in column B. Thanks.

How to Save As a macro-enabled or binary workbook? I click the [Save] button in this screenshot, not sure does this [Save] button do "Save As a macro-enabled or binary workbook"?

After clicking the [Save] button in the above screenshot, every time I open the excel sheet I need to redo the VB editor steps again, is it normal?

Locate the "Developer" tab. If you don't see it then

1. On the File tab, go to Options > Customize Ribbon.
2. Under Customize the Ribbon and under Main Tabs, select the Developer checkbox

Once you have the developer tab,

1. Alt + F11. This will bring up the VB editor.
2. Insert -> Module -> Paste in the code.
3. Press F5 to execute.
4. Check the result.

 

[man]

You need to Save As a macro-enabled or binary workbook since you're using VBA now. This will output the sheet name in column B.

Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
 
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
 
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 2)
 
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           b(k, 2) = ws.Name
                           k = k + 1
                        End If
                        On Error GoTo 0 ' Reset error handling
                    Next j
                Next i
            End If
        End If
    Next ws
 
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 2).Value = b
    End If
End Sub

If I want to search for more words (for example 4 words: Sold, Disposable, Bottle, Reusable), is it add one more row

InStr(1, a(i, j), "Reusable", vbTextCompare) > 0 And _

below this row

If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _

-----

If I want to change the search word to other word, is it just replace the word Sold/Disposable/Bottle seen in the code to other word I want to search? Is the search word case sensitive?

 

[Cubist]

Yes, Save As macro-enabled or binary so it doesn't undo your code.
Yes, add a line for additional words.
No, They're not case-sensitive but when you're searching for plurals don't include the "s" e.g. "bottle" instead of "bottles". If you search the plural it won't return singular, but if you search singular it will return both singular and plural.

 

[man]

Yes, Save As macro-enabled or binary so it doesn't undo your code.
Yes, add a line for additional words.
No, They're not case-sensitive but when you're searching for plurals don't include the "s" e.g. "bottle" instead of "bottles". If you search the plural it won't return singular, but if you search singular it will return both singular and plural.

How to Save As macro-enabled or binary so it doesn't undo my code, click where? Now every time I open the excel sheet I need to redo the VB editor steps again.

If I search for plural is it the result will show plural only? e.g. if I search for "bottles" will the result show "bottles"?

 

[Cubist]

F12 ->Save as type -> Pick Macro enabled or binary
Correct. It will not return "I sold a disposable water bottle".

 

[man]

F12 ->Save as type -> Pick Macro enabled or binary
Correct. It will not return "I sold a disposable water bottle".

The saved file will be .xlsm or .xlsb correct?

 

[Cubist]

yes.