I have a macro that loops the script below. It works fine it cannot find any more "***ERRROR*** b/c they are all deleted at the end. I am just trying to have the loop stop when it Errors out. The error is "Run-time error '91': Object variable or With block variable not set".
Sub TransposeLoop()
Application.ScreenUpdating = False
Do
Columns("A:A").Select
Selection.Find(What:="***ERROR***", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 0).Select
Selection.ClearContents
Selection.End(xlUp).Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
Range("A1").Select
Loop
Application.ScreenUpdating = True
End Sub
Sub TransposeLoop()
Application.ScreenUpdating = False
Do
Columns("A:A").Select
Selection.Find(What:="***ERROR***", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 0).Select
Selection.ClearContents
Selection.End(xlUp).Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
Range("A1").Select
Loop
Application.ScreenUpdating = True
End Sub