paddysheeran
04-17-2009, 05:27 AM
Hi All. I'm having problems with the following. My procedure is desgined to look for a value in one sheet, once it finds the value it copies all the cells underneath it and then pastes these cells into a different workbook. The procedure works fine until the last value is searched (Premium) - at this point:
Rows("2:2").Find(What:="PREMIUM", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
I get the error message:
Run-time error '91':
Object variable or With block variable not set
There is no data for Premium so that may have something to do with it but there is also no data for Priority 1 and that part of the procedure works fine.... help!!!
Sub Find()
'SVD Assigned
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
Rows("2:2").Find(What:="SVD Assigned", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("B6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
End With
'P1
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P1
Rows("2:2").Find(What:="PRIORITY 1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("C6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P1:
End With
'P2
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P2
Rows("2:2").Find(What:="PRIORITY 2", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("D6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P2:
End With
'P3
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P3
Rows("2:2").Find(What:="PRIORITY 3", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("E6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P3:
End With
'P4
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P4
Rows("2:2").Find(What:="PRIORITY 4", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("F6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P4:
End With
'UDP1
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_UDP1
Rows("2:2").Find(What:="UDP1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("G6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_UDP1:
End With
'UDP2
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_UDP2
Rows("2:2").Find(What:="UDP2", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("H6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_UDP2:
End With
'P5
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P5
Rows("2:2").Find(What:="PRIORITY 5", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("J6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P5:
End With
'Premium
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_PREMIUM
Rows("2:2").Find(What:="PREMIUM", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("I6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_PREMIUM:
End With
Range("A1").Select
End Sub
Rows("2:2").Find(What:="PREMIUM", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
I get the error message:
Run-time error '91':
Object variable or With block variable not set
There is no data for Premium so that may have something to do with it but there is also no data for Priority 1 and that part of the procedure works fine.... help!!!
Sub Find()
'SVD Assigned
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
Rows("2:2").Find(What:="SVD Assigned", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("B6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
End With
'P1
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P1
Rows("2:2").Find(What:="PRIORITY 1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("C6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P1:
End With
'P2
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P2
Rows("2:2").Find(What:="PRIORITY 2", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("D6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P2:
End With
'P3
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P3
Rows("2:2").Find(What:="PRIORITY 3", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("E6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P3:
End With
'P4
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P4
Rows("2:2").Find(What:="PRIORITY 4", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("F6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P4:
End With
'UDP1
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_UDP1
Rows("2:2").Find(What:="UDP1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("G6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_UDP1:
End With
'UDP2
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_UDP2
Rows("2:2").Find(What:="UDP2", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("H6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_UDP2:
End With
'P5
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_P5
Rows("2:2").Find(What:="PRIORITY 5", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("J6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_P5:
End With
'Premium
Rows("2:2").Select
On Error Resume Next
With Resolver_Open
On Error GoTo No_PREMIUM
Rows("2:2").Find(What:="PREMIUM", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Windows("CEC Weekly Performance Report -Template.xls").Activate
Range("I6").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Windows("CEC_Calculations.xls").Activate
Application.CutCopyMode = False
No_PREMIUM:
End With
Range("A1").Select
End Sub