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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Good morning.

I packed it.

In plan1 column A I have a list of 20 different words. In plan2 Column C I have complex texts in every cell.

I need to go through plan2 Column C looking for words contained in plan1 Column A if the word is found in Plan2 Column D in> "found" , if not "hidden."

Thank you!

excel vba

asked by anonymous 19.07.2018 / 15:54

2 answers

Problem with accented words coming from the MySql database Error in mat-select when I try to choose an item

score 1 · Answer 1

The code below goes through column C of plan2 by looking for the words in column A of plan1 by typing 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

score 0 · Answer 2

For this you do not need to use VBA, a formula in Excel would already suit you: = SE (SEERRO (LOCATE (A1; Plan2! $ A $ 1); 0) > 0; "Found"; "Hidden")

But if you need it done in VBA it's 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