+ Reply to Thread
Results 1 to 4 of 4

search for multiple words

Hybrid View

  1. #1
    Registered User
    Join Date
    01-29-2020
    Location
    Germany
    MS-Off Ver
    2019
    Posts
    2

    search for multiple words

    Hello everyone!

    I wrote a VBA program to search column A for the word "hello" in a table. As soon as the word is found in the column, the program transfers the row in the column to the right to a new table. This works fine. Now I want to search for several words at once. Unfortunately, this doesn't work ... Can someone look at the VBA code and tell me what I did wrong? Many thanks in advance!

    The Code

    Sub test()
    T = "T1"
    X = "A"
    AX = 1

    Z = "T4"
    Y = 2
    AY = "B"

    Do Until Suche <> ""
    Suche = ("hello"; "my"; "another")

    Loop
    Set A = Worksheets(T)
    Set B = Worksheets(Z)
    Y = Y

    With A.Columns(X)
    Set Gefunden = .Find(Suche, LookIn:=xlValues)
    If Not Gefunden Is Nothing Then
    Erste = Gefunden.Address
    Do 'für alle Fundstellen
    B.Cells(Y, AY).Resize(1, AX) = Gefunden.Offset(0, 1).Resize(1, AX).Value
    Y = Y + 1
    Set Gefunden = .FindNext(Gefunden)
    Loop Until Gefunden.Address = Erste
    End If
    End With
    End Sub

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: search for multiple words

    You could declare Suche as variant and load array of search words.

    May be something like below... this is untested.
    Sub test()
    Dim Suche
    T = "T1"
    X = "A"
    AX = 1
    
    Z = "T4"
    Y = 2
    AY = "B"
    
    Suche = Array("hello", "my", "another")
    
    Set A = Worksheets(T)
    Set B = Worksheets(Z)
    Y = Y
    For i = 0 To UBound(Suche)
        With A.Columns(X)
            Set Gefunden = .Find(Suche(i), LookIn:=xlValues)
            If Not Gefunden Is Nothing Then
            Erste = Gefunden.Address
                Do 'für alle Fundstellen
                B.Cells(Y, AY).Resize(1, AX) = Gefunden.Offset(0, 1).Resize(1, AX).Value
                Y = Y + 1
                Set Gefunden = .FindNext(Gefunden)
                Loop Until Gefunden.Address = Erste
            End If
        End With
    Next i
    End Sub
    "Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
    ― Robert A. Heinlein

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: search for multiple words

    Sub test()
        Dim Suche As Variant, Flurbin As Variant
        Dim Gefunden As Range, Erste As String, Y As Long
        
        Suche = Array("hello", "my", "another")
        Y = 2
        
        With Worksheets("T1").Columns("A")
            For Each Flurbin In Suche
                Set Gefunden = .Find(Flurbin, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
                If Not Gefunden Is Nothing Then
                    Erste = Gefunden.Address
                    Do    'für alle Fundstellen
                        Worksheets("T4").Cells(Y, "B").Value = Gefunden.Offset(0, 1).Value
                        Y = Y + 1
                        Set Gefunden = .FindNext(Gefunden)
                    Loop Until Gefunden.Address = Erste
                End If
            Next Flurbin
        End With
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    01-29-2020
    Location
    Germany
    MS-Off Ver
    2019
    Posts
    2

    Re: search for multiple words

    It works Thanks a lot!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search multiple cells for multiple words and receive back....
    By Saintom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2019, 04:41 PM
  2. Search for Values that Multiple Search Words Share
    By Drew67209 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2014, 05:54 AM
  3. search a column for multiple words
    By jbrennan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-21-2014, 10:23 AM
  4. Search for multiple words
    By airbender in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2013, 08:18 AM
  5. [SOLVED] Search box in Excel to search key words in sentences in multiple worksheets
    By fernandoii676 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2012, 01:42 PM
  6. Replies: 2
    Last Post: 10-19-2012, 11:11 AM
  7. Search Multiple Words in a Column
    By homebuyinghq in forum Excel General
    Replies: 1
    Last Post: 09-24-2007, 01:24 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1