0

I have a VBA macro which filters data and copies the results to another sheet. Everything was fine when the football seasons around the world were in full swing, but now with many having completed their interrupted seasons, there is less data. What happens is when there is NO data to transfer, I get an runtime error 1004 message "No cells were found". There are a number of these macros which are called in order, so when this stops, it causes the others to not proceed.

Here is the code

   Dim arr, ws As Worksheet, lc As Long, lr As Long

    arr = Array("S.EPL-DE-AUT-Home-Win", "S.Gre_HomeWin", "S.Ger_EPL_NL_Pol_SPL_HomeWin", _
                "S.DE_EPL_LALiga_Big_Odds_Jolly")

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    With ws.Range("A1", ws.Cells(lr, lc))
        .HorizontalAlignment = xlCenter
        .AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
    End With
      
    Workbooks("Predictology-Reports.xlsx").Sheets("Football Profits") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
End Sub

Is there a way to have it ignore no cells to copy? I can then make the same amendment in all the other ones.

cheers in advance

Just to update, the code was changed to add an IF to exit sub if no data. Here is the amended code:

Sub FALAYS()
   Dim arr, ws As Worksheet, lc As Long, lr As Long

    arr = Array("L.FAL_19_New_Summer2", "L.FA_FAL_3", "L.FAL_19_New_Summer")

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    With ws.Range("A1", ws.Cells(lr, lc))
        .HorizontalAlignment = xlCenter
        .AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
        If .Rows.Count - 1 > 0 Then
            .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
    Else
            Exit Sub
        End If
    End With
      
    Workbooks("Predictology-Reports.xlsx").Sheets("FAL") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
End Sub

Unfortunately if there is no data, it still shows the run-time error 1004. No cells were found on the line .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy. Any thoughts on what is not correct?

honkin
  • 113
  • 2
  • 12
  • 1
    At which line do you get a RTE 1004. I would expext a RTE 91 at this line: `lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row` because one needs to check if the `Find` method did give you a result, look [here](https://stackoverflow.com/questions/29066633/find-if-find-method-returns-nothing-in-excel-vba) – Storax Aug 06 '20 at 05:52
  • 1
    Other issue might be that your autofilter result is empty, check [this](https://stackoverflow.com/a/33259925/6600940) answer. – Storax Aug 06 '20 at 05:59
  • Hi, yes, the autofilter result IS empty. That was my point. If there is NO data to copy, the macro falls over, causing the others that are called in a separate macro to not run. There are maybe 6 or 7 of these, each with different arrays and which go to different sheets in the new workbook. The line it occurs on is always the `.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy` line. cheers – honkin Aug 06 '20 at 06:31
  • Then check the link in my second comment. – Storax Aug 06 '20 at 06:43
  • cheers @Storax Yes, I can see the use of `On Error Resume Next`, but have no idea how to code that. The multiple macros are all run from a single macro called Run_All_Predictology, which calls the first macro, then calls another which resets the sheet, then calls the next macro and so on. I am just unsure where to add the additional code so it moves to the next macro. – honkin Aug 06 '20 at 09:31
  • Try to write IF statement, where you will say, that if no data, then exit sub. – Geographos Aug 06 '20 at 10:06
  • Cheers @mkr I amended the code above with the IF statement, yet it still fall over on the copy line – honkin Aug 13 '20 at 10:07

0 Answers0