I want to loop through a range of cells to see if the words 'Grand Total'
exist. If it does, one message box comes up, if not, another box.

Here is the code I am using:

Sub FindLine()

'Pick the starting point
Range("I11").Select

'Loop until cell is Grand Total
Do Until ActiveCell = "Grand Total"

'If Grand Total is found, use this msgbox, exit do.
MsgBox "Please delete extra rows", vbOKOnly, "Extra Rows Found"
Exit Do

'If the active cell is not Grand Total, move the next row, loop
ActiveCell.Offset(1, 0).Select

'If Grand Total is not found, use this msgbox
MsgBox "Please Extend Range"
Loop

End Sub

The code stops at the first cell and exits. What is the proper way to move
the next row. In Access I would use .movenext.

Thanks!
PJ

Solved!

Found on http://support.microsoft.com/kb/299036

Sub Test3()
Dim x As String
Dim Found As Boolean
' Select first line of data.
Range("I11").Select
' Set search variable value.
x = "Grand Total"
' Set Boolean variable "found" to false.
Found = False
' Set Do loop to stop at empty cell.
Do Until IsEmpty(ActiveCell)
' Check active cell for search value.
If ActiveCell.Value = x Then
Found = True
Exit Do
End If
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
' Check for found.
If Found = True Then
MsgBox "Value found in cell " & ActiveCell.Address & ". Please
delete extra rows", vbOKOnly, "Extra Rows Found"
Else
MsgBox "Please extend the formulas", vbOKOnly, "Extension Needed"
End If
End Sub

"PJFry" wrote:

I want to loop through a range of cells to see if the words 'Grand Total'
exist. If it does, one message box comes up, if not, another box.

Here is the code I am using:

Sub FindLine()

'Pick the starting point
Range("I11").Select

'Loop until cell is Grand Total
Do Until ActiveCell = "Grand Total"

'If Grand Total is found, use this msgbox, exit do.
MsgBox "Please delete extra rows", vbOKOnly, "Extra Rows Found"
Exit Do

'If the active cell is not Grand Total, move the next row, loop
ActiveCell.Offset(1, 0).Select

'If Grand Total is not found, use this msgbox
MsgBox "Please Extend Range"
Loop

End Sub

The code stops at the first cell and exits. What is the proper way to move
the next row. In Access I would use .movenext.

Thanks!
PJ

Look in the vba help index for FINDNEXT. There is a good example you can
change to suit.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PJFry" wrote in message
...
I want to loop through a range of cells to see if the words 'Grand Total'
exist. If it does, one message box comes up, if not, another box.

Here is the code I am using:

Sub FindLine()

'Pick the starting point
Range("I11").Select

'Loop until cell is Grand Total
Do Until ActiveCell = "Grand Total"

'If Grand Total is found, use this msgbox, exit do.
MsgBox "Please delete extra rows", vbOKOnly, "Extra Rows Found"
Exit Do

'If the active cell is not Grand Total, move the next row, loop
ActiveCell.Offset(1, 0).Select

'If Grand Total is not found, use this msgbox
MsgBox "Please Extend Range"
Loop

End Sub

The code stops at the first cell and exits. What is the proper way to
move
the next row. In Access I would use .movenext.

Thanks!
PJ

You don't really need to look cell by cell. Give this a try...

If CBool(Application.CountIf(Columns("I"), "Grand Total")) Then
MsgBox "Please delete extra rows", vbOKOnly, "Extra Rows Found"
Else
MsgBox "Please Extend Range"
End If

assuming Grand Total is not in Cells I1:I10...
--
HTH...

Jim Thomlinson


"PJFry" wrote:

I want to loop through a range of cells to see if the words 'Grand Total'
exist. If it does, one message box comes up, if not, another box.

Here is the code I am using:

Sub FindLine()

'Pick the starting point
Range("I11").Select

'Loop until cell is Grand Total
Do Until ActiveCell = "Grand Total"

'If Grand Total is found, use this msgbox, exit do.
MsgBox "Please delete extra rows", vbOKOnly, "Extra Rows Found"
Exit Do

'If the active cell is not Grand Total, move the next row, loop
ActiveCell.Offset(1, 0).Select

'If Grand Total is not found, use this msgbox
MsgBox "Please Extend Range"
Loop

End Sub

The code stops at the first cell and exits. What is the proper way to move
the next row. In Access I would use .movenext.

Thanks!
PJ