Create macro to find and mark all cells that contain a particular "keyword"

[graememy]

Hi there

I have an excel spreadsheet containing many quotations said by famous people. These quotations are all in column A, taking up thousands of rows.

I want to create a macro that will find all the cells that contain a particular keyword of my choice (e.g. "happiness") and then put an "x" in column b adjacent to all the cells that contain that keyword.

Thanks very much for you assistance with this.

Cheers,
Graeme

 

[HalfAce]

Hi and welcome to the board.
How about something like this?

Sub SearchDemo()
Dim LstRw As Long, SrchTerm As String, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

SrchTerm = InputBox("What do you want to search for?")
If Len(SrchTerm) = 0 Then Exit Sub
For Rw = 1 To LstRw
  If InStr(Cells(Rw, "A"), SrchTerm) Then Cells(Rw, "B") = "X"
Next Rw

End Sub

Note that inserting the line: Option Compare Text at the very top of your code module will make it so the inputbox entry is not case sensitive.

Hope it helps.

 

[Rick Rothstein]

Instead of an "X" in the next column, what if we color the cell yellow so it stands out more? By the way, this macro should be somewhat fast as it uses no looping. Also, I made the code perform a case-insensitive search (just in case the word appears at the beginning of a sentence as well as internal to a sentence).

Sub FindWord()
  Dim Word As String
  Word = InputBox("What text do you want to find?")
  If Len(Word) Then
    Columns("A").Interior.ColorIndex = xlColorIndexNone
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
    Application.ReplaceFormat.Clear
  End If
End Sub

 

[graememy]

That's perfect. Does exactly what I want. Thanks so much!

 

[marzbarz]

Instead of an "X" in the next column, what if we color the cell yellow so it stands out more? By the way, this macro should be somewhat fast as it uses no looping. Also, I made the code perform a case-insensitive search (just in case the word appears at the beginning of a sentence as well as internal to a sentence).

Sub FindWord()
  Dim Word As String
  Word = InputBox("What text do you want to find?")
  If Len(Word) Then
    Columns("A").Interior.ColorIndex = xlColorIndexNone
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
    Application.ReplaceFormat.Clear
  End If
End Sub

I'm trying to do a similar thing, except I have multiple words that I am searching more. And they don't vary, so how would I modify this so that it doesn't have an input text box.

I have a list of customer names and I am sorting them by whether or not they have words such as city, council, government, ect.. (I have 26 keywords).

 

[Rick Rothstein]

I'm trying to do a similar thing, except I have multiple words that I am searching more. And they don't vary, so how would I modify this so that it doesn't have an input text box.

I have a list of customer names and I am sorting them by whether or not they have words such as city, council, government, ect.. (I have 26 keywords).

Give this modified version of my function a try (replace the etc. with the rest of your list with each individual item quoted)...

Sub FindWord()
  Dim Word As Variant
  Columns("A").Interior.ColorIndex = xlColorIndexNone
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.ColorIndex = 6
  For Each Word In Array("city", "council", "government", "etc.")
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
  Next
  Application.ReplaceFormat.Clear
End Sub

 

[marzbarz]

PERFECT! Works like a charm! Thank you very much.

 

[icarrascolozano]

Instead of an "X" in the next column, what if we color the cell yellow so it stands out more? By the way, this macro should be somewhat fast as it uses no looping. Also, I made the code perform a case-insensitive search (just in case the word appears at the beginning of a sentence as well as internal to a sentence).

Sub FindWord()
  Dim Word As String
  Word = InputBox("What text do you want to find?")
  If Len(Word) Then
    Columns("A").Interior.ColorIndex = xlColorIndexNone
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
    Application.ReplaceFormat.Clear
  End If
End Sub

Hey!

What if I need to look for multiple words at once? (i.e. apples, bananas, pineapples)

Thanks!

 

[Rick Rothstein]

Hey!

What if I need to look for multiple words at once? (i.e. apples, bananas, pineapples)

Use the code I posted after the one you quoted (Message #6) which I created for someone who asked your same question.

 

[icarrascolozano]

Hi and welcome to the board.
How about something like this?

Sub SearchDemo()
Dim LstRw As Long, SrchTerm As String, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

SrchTerm = InputBox("What do you want to search for?")
If Len(SrchTerm) = 0 Then Exit Sub
For Rw = 1 To LstRw
  If InStr(Cells(Rw, "A"), SrchTerm) Then Cells(Rw, "B") = "X"
Next Rw

End Sub

Note that inserting the line: Option Compare Text at the very top of your code module will make it so the inputbox entry is not case sensitive.

Hope it helps.

Hey!

What if I need to look for multiple words at once? (i.e. apples, bananas, pineapples)

Thanks!

Hi and welcome to the board.
How about something like this?

Sub SearchDemo()
Dim LstRw As Long, SrchTerm As String, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

SrchTerm = InputBox("What do you want to search for?")
If Len(SrchTerm) = 0 Then Exit Sub
For Rw = 1 To LstRw
  If InStr(Cells(Rw, "A"), SrchTerm) Then Cells(Rw, "B") = "X"
Next Rw

End Sub

Note that inserting the line: Option Compare Text at the very top of your code module will make it so the inputbox entry is not case sensitive.

Hope it helps.

Sorry I meant this one... Instead of an input box I have a cell where people can search for different keywords separated by comma. So whenever one of those keywords appear in the column the next column is marked with an "X".

Would I need a loop? Or what would be the best way to proceed? Many thanks in advance!

Please let me know if unclear.