I have a problem getting the Excel Search function to work in a Macro?

Question

I have a spreadsheet of about 1000 rows and want to write a macro that searches each row for the word "Sum" and then changes column next to it to "Sum Found". How do I create a VB code to search a cell for the word "Sum"?
The data in Cell A1 is either:
"Single" or "Sum" or "Total Sum"
Cell A2 looks like this:
=IF((ISERROR(SEARCH("Sum",A1))=FALSE,"SumFound","")

It said something about the fact that I should use the Application.WorksheetFunction. thing in VB, but I can't get it to work

Thanks for the answers so far, but another problem is creeping up namely spaces. The guy that gave me the worksheets has spaces in front of the sum, like " Sum" in a lot of the of the cells and the amount of spaces are not consistent. How do I get around it with a Macro?

Answer 1

Try using the Find method instead. I'm not a fan of either of the first two answers. Those are slower and more imprecise methods of doing that. And like you said, those are looking for exact matches, this will look for any occurrence.

Sub Macro1()

Dim x As Range
Dim FirstCell As String

On Error GoTo NoValue

Set x = Cells.Find(What:="Sum", _
After:=ActiveCell, _
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

FirstCell = x.Address
x.Offset(0, 1).Value = "Sum Found"

Do
Set x = Cells.FindNext(After:=x)
If x <> "Sum Found" Then
x.Offset(0, 1).Value = "Sum Found"
End If
Loop Until x.Address = FirstCell

NoValue:
Exit Sub
End Sub

This code searches all of the cells in the work sheet. Cells that have the word Sum in them are located and the cell directly to the right of each of those cells is changed to "Sum Found".

Answer 2

Here is the macro that I created to find only the word Sum.

For x = 1 To 1000
Set curCell = Worksheets("Sheet1").Cells(x, 1)
If curCell = "Sum" Then
Range("B" & x).Value = "Sum Found"
End If
Next x

Take care,
Troy

Answer 3

The previous answer is a perfectly fine solution, but here is another way to do things. I prefer to use the "while" method

Range("A1").Select
i = 0
While ActiveCell.Offset(i, 0) <> ""
If ActiveCell.Offset(i, 0) = "Sum" Then
ActiveCell.Offset(i, 1) = "Sum Found"
End If
i = i + 1
Wend