Hello,
My worksheet has a couple hundred columns and 20k rows. My task is to search for specific keywords in the text within each cell within each row and then write in "yes" or "no" if there is a match in the first column of each row. For example: searching for "dog", "cat", or "horse' in multiple cells of a row all containing various strings of text, if there is a match to any of those I'd enter "yes" in cell A1, and "no" if there is no match. I've figured out the search and write aspect of this task for a single line, but when I attempt to add in any functionality to fill down or repeat the formula for every row in my matrix the code fails. How do I get the macro to repeat the search and write functionality for every row in my matrix?
My test search range would be in cells "B8:D20".
My write-to cells would be in column A of each row. "A8:A20"
Sub Search_Range_For_Text()
Dim cell As Range
Dim row As Range
Dim rng As Range
Set rng = Range("b8:d20")
For Each row In rng.Rows
For Each cell In row.Cells
If InStr(cell.Value, "dog") > 0 Then
Cells(row.Rows, 1).Value2 = "yes" //this is my problem line
ElseIf InStr(cell.Value, "cat") > 0 Then
Cells(row.Rows, 1).Value2 = "yes"
ElseIf InStr(cell.Value, "horse") > 0 Then
Cells(row.Rows, 1).Value2 = "yes"
Else
Cells(row.Rows, 1).Value2 = "no"
End If
Next cell
Next row
End Sub
Thanks in advance for any help offered.
My worksheet has a couple hundred columns and 20k rows. My task is to search for specific keywords in the text within each cell within each row and then write in "yes" or "no" if there is a match in the first column of each row. For example: searching for "dog", "cat", or "horse' in multiple cells of a row all containing various strings of text, if there is a match to any of those I'd enter "yes" in cell A1, and "no" if there is no match. I've figured out the search and write aspect of this task for a single line, but when I attempt to add in any functionality to fill down or repeat the formula for every row in my matrix the code fails. How do I get the macro to repeat the search and write functionality for every row in my matrix?
My test search range would be in cells "B8:D20".
My write-to cells would be in column A of each row. "A8:A20"
Sub Search_Range_For_Text()
Dim cell As Range
Dim row As Range
Dim rng As Range
Set rng = Range("b8:d20")
For Each row In rng.Rows
For Each cell In row.Cells
If InStr(cell.Value, "dog") > 0 Then
Cells(row.Rows, 1).Value2 = "yes" //this is my problem line
ElseIf InStr(cell.Value, "cat") > 0 Then
Cells(row.Rows, 1).Value2 = "yes"
ElseIf InStr(cell.Value, "horse") > 0 Then
Cells(row.Rows, 1).Value2 = "yes"
Else
Cells(row.Rows, 1).Value2 = "no"
End If
Next cell
Next row
End Sub
Thanks in advance for any help offered.