Move data to table only if it doesnt exist in table

[DanSMT]

Hi all,

I'm stuck on this one. Trying to pull data from one sheet into a table of another sheet only if the two specific criteria do not exist. Code below.

Sub Button3_Click()
    
    Dim wsSource As Worksheet, wsDestination As Worksheet
    Dim LastRowSource As Long, LastRowDestination As Long
    Dim i As Long, y As Long
    Dim value_1 As String, Value_2 As String
    Dim ValueExists As Boolean
    
    With ThisWorkbook
        Set wsSource = .Worksheets("Data Dump")
        Set wsDestination = .Worksheets("YEAR-Test")
    End With
    
    With wsSource
    
        'Find the last row of Column A, wsSource
        LastRowSource = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'Loop Column A, wsSource
        For i = 1 To LastRowSource
        
            'Testing Columns F & G
            value_1 = .Range("F" & i).Value
            Value_2 = .Range("G" & i).Value
            
            ValueExists = False
            
            With wsDestination
            
                'Find the last row of Column A, wsDestination
                LastRowDestination = .Cells(.Rows.Count, "A").End(xlUp).Row
                
                'Loop Column A, wsDestination
                For y = 1 To LastRowDestination
                
                'For Each value_1 In Table1
                    If .Range("F" & y).Value = value_1 And .Range("G" & y).Value = Value_2 Then
                        ValueExists = True
                        Exit For
                        GoTo skip
                    'Else
                    End If
                    
                Next y
                
                'If value does not exist copy
                If ValueExists = False Then
                    .Range("F" & LastRowDestination + 1).Value = value_1
                    .Range("A" & LastRowDestination + 1).Value = Value_2
                    .Range("B" & LastRowDestination + 1).Value = wsSource.Range("L" & i).Value
                    .Range("D" & LastRowDestination + 1).Value = wsSource.Range("R" & i).Value
                    .Range("E" & LastRowDestination + 1).Value = wsSource.Range("D" & i).Value
                    .Range("G" & LastRowDestination + 1).Value = wsSource.Range("H" & i).Value
                    .Range("I" & LastRowDestination + 1).Value = wsSource.Range("C" & i).Value
                    .Range("J" & LastRowDestination + 1).Value = wsSource.Range("V" & i).Value
                    .Range("K" & LastRowDestination + 1).Value = wsSource.Range("M" & i).Value
                    .Range("M" & LastRowDestination + 1).Value = "=VLOOKUP(L5,Defects2!Print_Area,2,FALSE)"
                    .Range("O" & LastRowDestination + 1).Value = wsSource.Range("N" & i).Value
                    .Range("R" & LastRowDestination + 1).Value = wsSource.Range("O" & i).Value
                    .Range("S" & LastRowDestination + 1).Value = wsSource.Range("P" & i).Value
                End If
                'Next y

            End With
skip:
        Next i
        
    End With
    
End Sub

 

[igold]

It seems like a lot of code to accomplish what you describe. Although your description is succinct, is there any chance of seeing some of your data (real or fictitious) to get a better grasp of what you are exactly trying to accomplish. Also, you mention a table, but I do not see any table names in your code.

 

[DanSMT]

I can't show everything, however I can provide a little info.

This is the table (destination).

I've tried to add Table1[WO] to the script without success. I get a subscript error when doing so.

The information is pulled from a "Data Dump" worksheet that appears as the following.

Hope this helps.

 

[igold]

In looking at your data (sure would be nice if you used XL2BB) and looking at your code. It seems like in this part of your code:

'Testing Columns F & G
            value_1 = .Range("F" & i).Value
            Value_2 = .Range("G" & i).Value

you are assigning a WONumber (Col F) and a Date (Col G) to value_1 and Value_2 respectively from your source data, but then you are trying to match those to a WONumber and a Part # (Columns F & G) in the Destination table.

With wsDestination
            
                'Find the last row of Column A, wsDestination
                LastRowDestination = .Cells(.Rows.Count, "A").End(xlUp).Row
                
                'Loop Column A, wsDestination
                For y = 1 To LastRowDestination
                
                'For Each value_1 In Table1
                    If .Range("F" & y).Value = value_1 And .Range("G" & y).Value = Value_2 Then
                        ValueExists = True

Is that ever going to match up... Or should you be taking Column H from your source data as opposed to Column G...

 

[DanSMT]

You are correct. I missed that.

Fixed the error, but the script still seems to be pull data that is duplicate values.

 

[igold]

I have to be honest here, I would like to help you and I think I probably can, but for me to test code, means I would have to replicate your data by typing it in manually. I enjoy writing code. I do not enjoy doing "grunt" work, which that would be. That said you can use XL2BB and post your data or I am going to politely step aside.
You may want to check out this page to get started.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com