How to move the code on if no cells have been found

S

Snipes00

New Member
Joined
Aug 9, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hey All,

I'm getting the error that no cells have been found when running the following code. Although it is correct and no cells have been found under the criteria I've put, I want the code to just move on and continue with the code. Could anyone help me to do this? I am a beginner when it comes to VBA so if you have any other suggestions as to how my code could be more efficient feel free to say too. Any help would be appreciated and thank you in advance. My code is below.
VBA Code: 
Option Explicit
Sub Contractors()
Workbooks.Open ("C:\Users\ImaniS\OneDrive - Vodafone Group\Documents\Declaration Template\2022 Renewal Data\03. Property Damage & Business Interruption\3.5 Contractors All Risks.xlsx")
Dim lr1    As Long
Dim lr2    As Long
    With Workbooks("3.5 Contractors All Risks.xlsx").Sheets("Sheet1")
    lr1 = .Cells(Rows.Count, 1).End(xlUp).Row
        .AutoFilterMode = False
        .Range("A1:AY" & lr1).AutoFilter Field:=1, Criteria1:="GR01" 'filter range on EVO Code
        lr2 = Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Range("G9:H" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("A41").PasteSpecial xlPasteAll  'Copy/Paste
        .Range("K9:L" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("C41").PasteSpecial xlPasteAll
        .Range("N9:S" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("E41").PasteSpecial xlPasteAll
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
Workbooks("3.5 Contractors All Risks.xlsx").Close

Workbooks.Open ("C:\Users\ImaniS\OneDrive - Vodafone Group\Documents\Declaration Template\2022 Renewal Data\01.  Vodafone Information\1.5 Business Activities.xlsx")
    With Workbooks("1.5 Business Activities.xlsx").Sheets("Sheet1")
        .AutoFilterMode = False
        .Range("A1:AY" & lr1).AutoFilter Field:=1, Criteria1:="GR01" 'filter range on EVO Code
        .Range("A9:A" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B10").PasteSpecial Paste:=xlPasteAll 'Copy/Paste
        .Range("B9:B" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B11").PasteSpecial Paste:=xlPasteAll
        .Range("C9:C" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B12").PasteSpecial Paste:=xlPasteAll
        .Range("F9:F" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B13").PasteSpecial Paste:=xlPasteAll
        .Range("H9:H" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B14").PasteSpecial Paste:=xlPasteAll
        .Range("I9:I" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B15").PasteSpecial xlPasteAll
        .Range("J9:J" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B16").PasteSpecial xlPasteAll
        .Range("K9:K" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B19").PasteSpecial xlPasteAll
        .Range("L9:L" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B20").PasteSpecial xlPasteAll
        .Range("M9:M" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B21").PasteSpecial xlPasteAll
        .Range("O9:O" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B22").PasteSpecial xlPasteAll
        .Range("P9:P" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B23").PasteSpecial xlPasteAll
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
Workbooks("1.5 Business Activities.xlsx").Close
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Can you select debug the next time it errors and show which row it selects

Suspect you need to validate that lr1 and lr2 have a value
 
Upvote 0
Add an error trap on top:
VBA Code: 
Dim lr1    As Long
Dim lr2    As Long
'Here is added error trap
On Error resume Next
'-------
    With Workbooks("3.5 Contractors All Risks.xlsx").Sheets("Sheet1")
 
Upvote 0
Solution
nemmi69 said:
Can you select debug the next time it errors and show which row it selects

Suspect you need to validate that lr1 and lr2 have a value
Click to expand...
So the line it errors is as highlighted, but it makes sense as in the file 3.5 Contractors All Risks, there is no data that filters on my criteria of GR01. So it is right that there are no cells in this document, but I just want it to accept that and move on to the next part. I have to do this as there will be other criteria that when I filter on it will have a value so just a bit stuck, hope that helps.

VBA Code: 
Option Explicit
Sub Contractors()
Workbooks.Open ("C:\Users\ImaniS\OneDrive - Vodafone Group\Documents\Declaration Template\2022 Renewal Data\03. Property Damage & Business Interruption\3.5 Contractors All Risks.xlsx")
Dim lr1    As Long
Dim lr2    As Long
    With Workbooks("3.5 Contractors All Risks.xlsx").Sheets("Sheet1")
    lr1 = .Cells(Rows.Count, 1).End(xlUp).Row
        .AutoFilterMode = False
        .Range("A1:AY" & lr1).AutoFilter Field:=1, Criteria1:="GR01" 'filter range on EVO Code
        lr2 = Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Cells(Rows.Count, 1).End(xlUp).Row + 1
        [COLOR=rgb(250, 197, 28)].Range("G9:H" & lr1).SpecialCells(xlCellTypeVisible).Copy[/COLOR]
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("A41").PasteSpecial xlPasteAll  'Copy/Paste
        .Range("K9:L" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("C41").PasteSpecial xlPasteAll
        .Range("N9:S" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("E41").PasteSpecial xlPasteAll
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
Workbooks("3.5 Contractors All Risks.xlsx").Close

Workbooks.Open ("C:\Users\ImaniS\OneDrive - Vodafone Group\Documents\Declaration Template\2022 Renewal Data\01.  Vodafone Information\1.5 Business Activities.xlsx")
    With Workbooks("1.5 Business Activities.xlsx").Sheets("Sheet1")
        .AutoFilterMode = False
        .Range("A1:AY" & lr1).AutoFilter Field:=1, Criteria1:="GR01" 'filter range on EVO Code
        .Range("A9:A" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B10").PasteSpecial Paste:=xlPasteAll 'Copy/Paste
        .Range("B9:B" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B11").PasteSpecial Paste:=xlPasteAll
        .Range("C9:C" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B12").PasteSpecial Paste:=xlPasteAll
        .Range("F9:F" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B13").PasteSpecial Paste:=xlPasteAll
        .Range("H9:H" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B14").PasteSpecial Paste:=xlPasteAll
        .Range("I9:I" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B15").PasteSpecial xlPasteAll
        .Range("J9:J" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B16").PasteSpecial xlPasteAll
        .Range("K9:K" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B19").PasteSpecial xlPasteAll
        .Range("L9:L" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B20").PasteSpecial xlPasteAll
        .Range("M9:M" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B21").PasteSpecial xlPasteAll
        .Range("O9:O" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B22").PasteSpecial xlPasteAll
        .Range("P9:P" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B23").PasteSpecial xlPasteAll
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
Workbooks("1.5 Business Activities.xlsx").Close
End Sub
 
Upvote 0
bebo021999 said:
Add an error trap on top:
VBA Code: 
Dim lr1    As Long
Dim lr2    As Long
'Here is added error trap
On Error resume Next
'-------
    With Workbooks("3.5 Contractors All Risks.xlsx").Sheets("Sheet1")
Click to expand...
Thank you! This seems to work perfectly. Appreciate the help
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Locked
  • Question Question
How to write this code in one line
Replies
1
Views
588
Fluff
Fluff
S
  • Question Question
Help with simplifying code
Replies
1
Views
439
johnnyL
J
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
352
NdNoviceHlp
NdNoviceHlp
M
  • Question Question
VBA only copy data from one sheet that isn't already on another sheet
Replies
0
Views
635
mik1996
M
L
  • Solved
VBA Error
2 3
Replies
29
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top