If Cells.Find returns run-time error '91': Object variableor With block variable not set

[Tonso]

I have the following marco that I have used with success to search through donwloaded data and take an action if "No Schedule" is found in the data. It has stopped functioning now and i get....Run-time error '91': Object variable or With block variable not set. I do not understand why it worked formonths and now has stopped? The macro is below.

Sub FindNoSked()
If Cells.Find(What:="No Schedule", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False) = False Then
Exit Sub
End If
'copies No Schedule row if presentand appends
Cells.Find(What:="No Schedule", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Range("A1:N1").Select
Selection.Copy
Application.Goto Reference:="R1500C1"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
Application.CutCopyMode = False
End Sub

Thanks for any help!

Tonso

 

[Claus Busch]

Hi Tonso,

Am Tue, 13 Nov 2012 05:05:07 -0800 (PST) schrieb Tonso:

I have the following marco that I have used with success to search through donwloaded data and take an action if "No Schedule" is found in the data. It has stopped functioning now and i get....Run-time error '91': Object variable or With block variable not set. I do not understand why it worked for months and now has stopped? The macro is below.

the error comes if "No Schedule" is not found.
Try:

Sub FindNoSked()
Dim SRow As Long
Dim LRow As Long

On Error Resume Next
If Cells.Find(What:="No Schedule", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False) = False Then Exit Sub

'copies No Schedule row if presentand appends
SRow = Cells.Find(What:="No Schedule", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(SRow, "A"), Cells(SRow, "N")).Copy _
Destination:=Cells(LRow + 1, "A")

Application.CutCopyMode = False
End Sub


Regards
Claus Busch