bobaol
Board Regular
- Joined
- Jun 3, 2002
- Messages
- 225
- Office Version
- 365
- 2003 or older
- Platform
- Windows
Hello,
I am doing a loop (kind of, i suppose) by using multiple macros. In macro27, i want to find "Problem_Closed", but if "Problem_Closed" does not exist, then exit the sub.
my problems lies in that "Problem_Closed" may exist once or may exist a thousand instances.
I am using Macro28 and Macro29 to repeat Macro27. This is very inefficient and takes a lot of time. But it does work.
is there a simpler way?
here is my code.....
Sub Macro27_find_cut_and_paste_to_Closed()
ActiveSheet.UsedRange
Application.Goto Reference:="R1C1"
If Cells.Find What:="Problem_Closed_OR_Service_Closed", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True) Is Nothing Then
Exit Sub
End If
Cells.Find(What:="Problem_Closed_OR_Service_Closed", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate
ActiveCell.Offset(0, 5).Range("A1").Select
Calculate
Selection.End(xlToLeft).Select
ActiveCell.Range("A1:AN1").Select
Selection.Cut
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.UsedRange
End Sub
Sub Macro28_repeat_Macro27_find_cut_and_paste_to_Closed()
Application.Run "Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro27_find_cut_and_paste_to_Closed"
End Sub
Sub Macro29_repeat_Macro27_find_cut_and_paste_to_Closed()
Application.Run "Macro28_repeat_Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro28_repeat_Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro28_repeat_Macro27_find_cut_and_paste_to_Closed"
Calculate
End Sub
thanks in advance.
- bobaol
I am doing a loop (kind of, i suppose) by using multiple macros. In macro27, i want to find "Problem_Closed", but if "Problem_Closed" does not exist, then exit the sub.
my problems lies in that "Problem_Closed" may exist once or may exist a thousand instances.
I am using Macro28 and Macro29 to repeat Macro27. This is very inefficient and takes a lot of time. But it does work.
is there a simpler way?
here is my code.....
Sub Macro27_find_cut_and_paste_to_Closed()
ActiveSheet.UsedRange
Application.Goto Reference:="R1C1"
If Cells.Find What:="Problem_Closed_OR_Service_Closed", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True) Is Nothing Then
Exit Sub
End If
Cells.Find(What:="Problem_Closed_OR_Service_Closed", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate
ActiveCell.Offset(0, 5).Range("A1").Select
Calculate
Selection.End(xlToLeft).Select
ActiveCell.Range("A1:AN1").Select
Selection.Cut
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.UsedRange
End Sub
Sub Macro28_repeat_Macro27_find_cut_and_paste_to_Closed()
Application.Run "Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro27_find_cut_and_paste_to_Closed"
End Sub
Sub Macro29_repeat_Macro27_find_cut_and_paste_to_Closed()
Application.Run "Macro28_repeat_Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro28_repeat_Macro27_find_cut_and_paste_to_Closed"
Application.Run "Macro28_repeat_Macro27_find_cut_and_paste_to_Closed"
Calculate
End Sub
thanks in advance.
- bobaol