VBA - How to search words from one list in another and point "Found"

Asked

Viewed 1,823 times

0

Good morning.

Got stuck, in that.

In the plan1 column A I have a list with 20 different words. In the plan2 Column C I have complex texts in each cell.

I need to walk the plan2 Column C searching for words contained in plan1 Column A if the word is found in Plan2 Column D the message will appear "found", if not "occult".

Thank you so much!

excel vba
  • 2

    Mike, I don’t quite understand your problem. Post your code, an image of the spreadsheet, which you draw more clearly, because that way it gets complicated.

  • Welcome(a). Please do the [tour], then read How we should format questions and answers? and create a [mcve] for the question. Because the question is too wide and when you are more specific, there are more chances of your question being answered correctly. With an example table

2 answers

1

The code below goes through the column C of plan2 looking for the words in column A of plan1, writing in column D of plan2 "found", if you have found any of the words or "hidden", if you have not found any. Include the code below in a macro in your spreadsheet.

Sub Macro1()

    Dim linhas1 As Long
    Dim linhas2 As Long
    Dim i As Long
    Dim j As Long

    linhas1 = Worksheets("plan1").Cells(Rows.Count, 1).End(xlUp).Row
    linhas2 = Worksheets("plan2").Cells(Rows.Count, 3).End(xlUp).Row

    For i = 1 To linhas1
        For j = 1 To linhas2

            If (Worksheets("plan2").Cells(j, 4) <> "encontrado") Then

                a = InStr(1, Worksheets("plan2").Cells(j, 3), Worksheets("plan1").Cells(i, 1), 1)
                If (a = 0) Then
                    Worksheets("plan2").Cells(j, 4) = "oculto"
                Else: Worksheets("plan2").Cells(j, 4) = "encontrado"
                End If

            End If

        Next j
    Next i

End Sub

0

For this it is not necessary to use VBA, a formula in Excel would already serve you: =SE(SEERRO(FIND(A1;Plan2! $A$1);0)>0;"Found";"Hidden")

But if you need it done in VBA is something like:

Private Sub CommandButton1_Click()
    Dim texto As String, palavra As String

    texto = Sheets(2).Range("A1")
    palavra = Sheets(1).Range("A4")

    If texto Like "*" & palavra & "*" Then
        MsgBox "Encontrado"
    Else
        MsgBox "Oculto"
    End If
End Sub

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.