need help fixing macro

  • Thread starter Thread starter PVT
  • Start date Start date
P

PVT

Hi- can you please help me fix the following macro. It "hangs" when
"no" is chosen at the messagebox. How do I make it go to the next cell
in the "if" statement?

Sub DeleteSearchTerm26()
Dim c As Range

With ActiveSheet.Range("A:A")
Do

Set c = .Find("by", LookIn:=xlValues, lookat:=xlPart, _
MatchCase:=False)

If c Is Nothing Then Exit Do
c.Select

If MsgBox("Delete entire row?", vbYesNo) = vbYes Then
c.EntireRow.Delete Else ActiveCell.Offset
(0,1).Select


Loop
End With

End Sub
 
Put this in place of your If/Then

If MsgBox("Delete entire row?", vbYesNo) = vbYes Then
c.EntireRow.Delete
Else
ActiveCell.Offset(0, 1).Select
Exit Do
End If
 
Adding to Barb's comments, your Do Loop will be an infinite loop withou
specifying Until or While with eithe the Do or the Loop. i.e.

Do Until 'Something
Do While 'Something
Loop Until 'Something
Loop While 'Something

The Something needs to be a value or the absence of a value that the loop
will logically find. As it is written, it goes nowhere and checks nothing,
so it will just loop.
 
Back
Top