MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I make my macro finish when it can no longer find copy in column H?


Posted by Paul S on November 02, 2000 12:56 PM

How do I make the macro finish when it can no longer find "copy" in column H?

What the Macros do!!

Macro 1 puts a formula in column H, so if cell G is greater than 0 it displays "copy" in cell H.

Macro 2 then searchs for "copy" in column H and if it finds it it, it clears cell H and copies the entire row to another worksheet.

But, when macro 2 can no longer find "copy" in column H it stops and shows the error message "Object variable or With block variable not set". How can I overcome this problem?

The Macros are below!!

Sub Macro 1()
Application.Goto Reference:="Sheet1!R1C1"
Application.Goto Reference:="R3C8"
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,""copy"","""")"
Selection.Copy
ActiveCell.Range("A2:A51").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Application.CutCopyMode = False
Calculate
End Sub

Sub Macro2()

Application.Goto Reference:="Sheet1!R1C1"
Application.Goto Reference:="R1C8"
Cells.Find(What:="copy", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True).Activate
Selection.ClearContents
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Range("A1:G1").Select
Selection.Copy

Application.Goto Reference:="Sheet2!R1C1"
ActiveCell.Offset(6, 0).Range("A1").Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
ActiveCell.Range("A1:J1").Select
Selection.Insert Shift:=xlDown
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Application.Goto Reference:="Sheet1!R1C1"


Application.Goto Reference:="Sheet1!R1C1"
Application.Goto Reference:="R1C8"
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.ClearContents
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Range("A1:G1").Select
Selection.Copy

Application.Goto Reference:="Sheet2!R1C1"
ActiveCell.Offset(6, 0).Range("A1").Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
ActiveCell.Range("A1:J1").Select
Selection.Insert Shift:=xlDown
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Application.Goto Reference:="Sheet1!R1C1"


Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"
End Sub


Posted by Celia on November 02, 2000 2:40 PM


Paul
The two macros can be combined into one :-

Sub CombinedMacro()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Sheets("Sheet1").Range("H3:H53")
If cell.Offset(0, -1) > 0 Then
cell.EntireRow.Copy
Sheets("Sheet2").Rows("7:7").Insert Shift:=xlDown
End If
Next
Application.CutCopyMode = False
End Sub

Celia