Click here to Skip to main content
14,269,076 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

I have been browsing this forum and others as well to find a suitable answer, but haven't come across one that fits or works.

I have the following code:
'Filter data by PO and by material number
                For z = 2 To LastRowUqPO
                    'Filter by unique PO
                    On Error GoTo ErrorM
                    wbIT.Sheets("Bewerkingen").Range("A1:E" & LastRowBewerking).AutoFilter _
                    field:=2, Criteria1:=wbIT.Sheets("Bewerkingen").Range("G" & z).Value, Operator:=xlFilterValues
                    On Error GoTo 0
                    For w = 2 To LastRowUqMat
                        'Filter by unique material number
                        On Error GoTo ErrorL
                        wbIT.Sheets("Bewerkingen").Range("A1:E" & LastRowBewerking).AutoFilter _
                        field:=3, Criteria1:=wbIT.Sheets("Bewerkingen").Range("H" & w).Value, Operator:=xlFilterValues
                        On Error GoTo 0
                        ''Copy filtered data to new area
                        On Error GoTo ErrorL
                        Set rngCopy = wbIT.Sheets("Bewerkingen").Range("B2:C" & LastRowBewerking).SpecialCells(xlCellTypeVisible)
                        If rngCopy Is Nothing Then GoTo NextL
                        wbIT.Sheets("Bewerkingen").Range("B2:C" & LastRowBewerking).SpecialCells(xlCellTypeVisible).Copy
                        On Error GoTo 0


I have added the following error handling:
                        ''Error handling for NextM
ErrorM:                 If Err.Number = 1004 Then
                            GoTo NextM
                        Else
                            On Error GoTo 0
                        End If
                        ''Error handling for NextL
ErrorL:                 If Err.Number = 1004 Then
                            GoTo NextL
                        Else
                            On Error GoTo 0
                        End If


While I believe I have plenty of error handling, I still cannot get past this part of the code:
''Copy filtered data to new area
                        On Error GoTo ErrorL
                        Set rngCopy = wbIT.Sheets("Bewerkingen").Range("B2:C" & LastRowBewerking).SpecialCells(xlCellTypeVisible)
                        If rngCopy Is Nothing Then GoTo NextL


List of declarations:
Dim wbIT As Workbook
Dim wbSAP As Workbook
Dim wbPGTL As Workbook
Dim DataFile As String
Dim LastRow As Long
Dim LastCol As Long
Dim LastRowIO As Long
Dim LastColIO As Long
Dim LastRowIOP As Long
Dim LastRowFY As Long
Dim i As Long
Dim w As Long
Dim x As Long
Dim y As Long
Dim z As Long
Dim Folder As String
Dim StrFile As String
Dim findrow As Range
Dim findrownumber As Long
Dim LastRowPGTL As Long
Dim LastRowPO As Long
Dim LastRowUnique As Long
Dim LastRowBewerking As Long
Dim LastRowUqPO As Long
Dim LastRowUqMat As Long
Dim LastRowUnq As Long
Dim LastRowUnq2 As Long
Dim FirstRowSum As Long
Dim LastRowSum As Long
Dim PotentialIssue As String
Dim rngDupl() As Range
Dim rngCell As Range
Dim rngCheck As Range
Dim lduplicates As Long
Dim myPath As String
Dim myStart As String
Dim myFile As String
Dim fileType As String
Dim rngSort As Range
Dim rngKey As Range
Dim rngCopy As Range


Maybe you guys can see what I'm missing?

Error: 1004 No cells were found

The filtered data is indeed empty, which is why I need the code to go to the next iteration in the loop. In the current code, it is inevitable that there will be no visible cells in a certain filter combination, but they all need to be checked.

The code works perfectly as long as there are filtered values.

Should you require any further information, I would be happy to provide it.

Thank you in advance!

What I have tried:

On error GoTo NextL/NextM
if rngCopy is nothing then GoTo NextL/NextM
changing error trapping settings in Tools > Options
Posted
Updated 17-Jun-19 1:34am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Your code is incomplete, so it makes it unreadable. As to me, there's to many On Error commands...


A very basic usage of error handler and returning-subroutine (suitable for you) is:

Sub FilterAndCopyData()
    Dim wbIT As Workbook, wbDst
    'other variables
    Dim srcWsh As Worksheet, dstWsh As Worksheet
    Dim rangeToCopy As Range

    On Error Goto Err_FilterAndCopyData

    Set wbIT = Application.Worksbooks.Open("fullfilename")
    Set wbDst = ThisWorkbook
    Set srcWsh = wbIT.Worksheets("Bewerkingen")
    Set dstWsh = wbDst.Worksheets("AllData")

    For z = 2 To LastRowUqPO
        'Filter data
         Set rangeToCopy = srwsh.Range("A1:E" & LastRowBewerking).AutoFilter _
                    field:=2, Criteria1:=wbIT.Sheets("Bewerkingen").Range("G" & z).Value, Operator:=xlFilterValues
        'try to copy
        rangeToCopy.Copy
        'and paste
        dstWsh.Range("RangeToPasteData").PasteSpecial xlPasteValues

'subroutine where error handler returns in case of 1004 error message
NextRow:        
        Application.CutCopyMode = False
    Next z

Exit_FilterAndCopyData:
    'clean up
    On Error Resume Next
    Set rangeToCopy = Nothing
    'other instructions
    Exit Sub

Err_FilterAndCopyData:
    Select Case Err.Number
        Case 1004
            'go to the next row in a for-next loop
            Resume NextRow 'here!

        Case Else
            MsgBox Err.Description, vbExclamation, Err.Number
            Resume Exit_FilterAndCopyData
    End Select

End Sub


Good luck!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100