Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro loop with messagebox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro loop with messagebox
On Mar 6, 11:57*am, PVT wrote:
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 Should that be Inputbox instead of Msgbox? (I've forgotten the little Excel I knew so that may not be the case.) Kind regards, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro loop with messagebox
In a previous posting I tried to point out that your Do Loop
was not set up properly because it has no defined stopping point. It will run forever if it does not find the key word. Below is a macro that I believe will do what you are trying to do. Give it a try, and post back with any problems. Sub getBy() Dim rng As Range, lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) For i = lr To 2 Step -1 If LCase(ActiveSheet.Cells(i, 1).Value) Like "*by*" Then If MsgBox("DO YOU WANT TO DELETE ENTIRE ROW", _ vbYesNo, "Delete Row?") = vbYes Then ActiveSheet.Cells(i, 1).EntireRow.Delete End If End If Next End Sub "PVT" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro loop with messagebox
In case you need to see what is in the cell before deleting the row, you can
use this modified version. The message box will show what the found cell has in it. Sub getBy() Dim rng As Range, lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) For i = lr To 2 Step -1 If LCase(ActiveSheet.Cells(i, 1).Value) Like "*by*" Then If MsgBox("DO YOU WANT TO DELETE: " & Cells(i, 1).Value, _ vbYesNo, "Delete Row?") = vbYes Then ActiveSheet.Cells(i, 1).EntireRow.Delete End If End If Next End Sub "PVT" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro loop with messagebox
That was the intention- and it worked perfectly. Thank you for your
help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MessageBox | Excel Programming | |||
Multiline Messagebox in VBA | Excel Programming | |||
messagebox macro | Excel Programming | |||
messagebox help | Excel Programming | |||
messagebox help | Excel Programming |