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 the row 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","")

Answer 1

Try this
=IF(ISERROR(FIND("Sum",A1)),"Not Found","Sum Found")

Answer 2

Try using the Find method instead.

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(1, 0).Value = "Sum Found"

Do
Set x = Cells.FindNext(After:=x)
If x <> "Sum Found" Then
x.Offset(1, 0).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 below each of those cells is changed to "Sum Found".

This coding searches through the document similar to if you used Ctrl + F to bring up the find and Replace dialog box.