Search & Copy function based on criteria that EXCLUDES invalid entries

[idlewyld89]

Alright so I'm a good portion of the way through this, but I can't seem to solve one issue.

The function is intended to search a defined column for a defined criteria and copy the whole row if it's found. The issue is that, in addition to copying the rows with the criteria, it also copies "undefined" #N/A entries as well...

Trying to figure out how to exclude those values when they're found. Searched data is based on a VLOOKUP function.

Worth noting, the below functions simply give a missmatched datatype error, but to get around that I had put in a "On Error Resume Next / GoTo 0" around the IF statement. Tried the following two functions with the same results:

Private Sub SearchAndExtract(ByVal Column1 As String, ByVal Criteria1 As String, ByVal Destination As String, Optional ByVal Column2 As String, Optional ByVal Criteria2 As Integer)

Dim i As Integer: i = 0
Dim j As Integer: j = Worksheets(Destination).Range("A" & Rows.Count).End(xlUp).Row


LastRow = Worksheets(MainSheet).Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow


Worksheets(MainSheet).Select


    If Cells(i, ReturnColumnLetter2(Column1, MainSheet)) Is Nothing Then
    
        Worksheets(MainSheet).Rows(i).Delete
        LastRow = LastRow - 1


    ElseIf Cells(i, ReturnColumnLetter2(Column1, MainSheet)) = Criteria1 Then
        
        j = j + 1
        
        Worksheets(MainSheet).Rows(i).Copy
        Worksheets(Destination).Rows(j).PasteSpecial xlValues
        Worksheets(Destination).Rows(j).PasteSpecial xlFormats
    
    End If


Next


End Sub

Private Sub CopySearchedData(ByVal Column As String, ByVal Criteria1 As String, ByVal Destination As String, ByVal Exclude As Boolean)

Dim i: i = 0
Dim j: j = Worksheets(Destination).Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow


If (InStr(1, Worksheets(MainSheet).Range(Column + CStr(i)).Value, Criteria1) > 0 And Exclude = False) Then


    j = j + 1
    
    Worksheets(MainSheet).Rows(i).Copy
    Worksheets(Destination).Rows(j).PasteSpecial xlValues
    Worksheets(Destination).Rows(j).PasteSpecial xlFormats
    
ElseIf (Worksheets(MainSheet).Range(Column + CStr(i)).Value <> Criteria1 And Exclude = True) Then


    j = j + 1
    
    Worksheets(MainSheet).Rows(i).Copy
    Worksheets(Destination).Rows(j).PasteSpecial xlValues
    Worksheets(Desgination).Rows(j).PasteSpecial xlFormats
    
End If


Next


End Sub

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel...-based-criteria-excludes-invalid-entries.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.