VBA identified Tables to compare values and skip if match

[DThib]

Help!!!

I have a code to look at a result table (QA_Table) column to make sure the results are not duplicated when adding new entries from a worksheet to the table. These two worksheets are in the same workbook.

I had it working and it stopped working. I have been tinkering with it and it just repeats the same results already in the table!

Here is the code:

Sub Newt()


    Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
    Dim MBRow As Long, CoRow As Long, QArow As Long
    Dim m As Long, j As Long, l As Long, g As Long
    Dim x As Long, y As Long, a As Long
    Dim b1 As String
[COLOR=#b22222]    Dim TBL1, TBL2, TabC As Range[/COLOR]


    
    Set MBs = ThisWorkbook.Sheets("MB51_Draw")
    Set Coos = ThisWorkbook.Sheets("COOIS_Draw")
    Set QAws = ThisWorkbook.Sheets("QA_Data")
    
[COLOR=#b22222]    Set tbl = Sheets("QA_Data") '.ListObjects("QA_Table")[/COLOR]
[COLOR=#b22222]    With tbl[/COLOR]
[COLOR=#b22222]      Set TBL1 = .Range("C2", .Range("C1").End(xlDown))[/COLOR]
[COLOR=#b22222]    End With[/COLOR]
[COLOR=#b22222]    Set searchw = Sheets("COOIS_Draw")[/COLOR]
[COLOR=#b22222]    With searchw[/COLOR]
[COLOR=#b22222]       Set TBL2 = .Range("A2", .Range("A1").End(xlDown))[/COLOR]
[COLOR=#b22222]    End With[/COLOR]

[COLOR=#b22222]      For Each TabC In TBL1[/COLOR]
[COLOR=#b22222]         If WorksheetFunction.CountIf(TBL2, TabC) = 0 Then[/COLOR]
[COLOR=#b22222]            tbl.Range("C" & Rows.Count).End(xlUp) = TabC[/COLOR]
            
            MBRow = MBs.Cells(Rows.Count, "B").End(xlUp).Row
            CoRow = Coos.Cells(Rows.Count, "B").End(xlUp).Row
            QArow = QAws.Cells(Rows.Count, "C").End(xlUp).Row
                j = QArow + 1
                g = MBRow
                m = CoRow
                 For m = 1 To CoRow
                  For g = 1 To MBRow
                     If Coos.Cells(m, 1) = MBs.Cells(g, 13).Value And MBs.Cells(g, 12) Like "5*" Then
                            QAws.Cells(j, 1) = Coos.Cells(m, 4).Value
                            QAws.Cells(j, 2) = Coos.Cells(m, 5).Value
                            QAws.Cells(j, 3) = MBs.Cells(g, 13).Value
                            QAws.Cells(j, 4) = MBs.Cells(g, 17).Value
                            QAws.Cells(j, 5) = MBs.Cells(g, 14).Value
                            j = j + 1
                     End If
                  Next g
                 Next m
         End If
      Next
   
End Sub

Trouble Code is in red

Arrrrgh,
DThib

 

[NoSparks]

In words, (not code that doesn't work) what is it you want to do when the CountIf = 0

Here's what I think you're doing down to that point

Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
Dim cel As Range, rng1 As Range, rng2 As Range

Set MBs = ThisWorkbook.Sheets("MB51_Draw")
Set Coos = ThisWorkbook.Sheets("COOIS_Draw")
Set QAws = ThisWorkbook.Sheets("QA_Data")

'set the first range
With QAws
    Set rng1 = .Range("C2", .Range("C1").End(xlDown))
End With
'set the second range
With Coos
    Set rng2 = .Range("A2", .Range("A1").End(xlDown))
End With
'loop through the first range checking for existance in second range
For Each cel In rng1
    If WorksheetFunction.CountIf(rng2, cel.Value) = 0 Then
        '
        ' don't follow what you want to do here
        '
    End If
Next cel

 

[DThib]

NoSparks,

Thank you for the suggestion.
The code you suggested pulls nicely, but it is the placing the same match that is already present in the QA_Table.
I want to skip those and place only new matches in that table to avoid duplication.

Any other thoughts?

 

[NoSparks]

but it is the placing the same match that is already present in the QA_Table

I have done nothing to address that part because I can't figure out what you're trying to do.




This line over writes the last used cell in column C on sheet "QA_Data"

            tbl.Range("C" & Rows.Count).End(xlUp) = TabC

What are your two inner loops supposed to be doing ?

What does this line determine ?

If Coos.Cells(m, 1) = MBs.Cells(g, 13).Value And MBs.Cells(g, 12) Like "5*" Then

 

[DThib]

Hi,

I have removed this, it was one of my attempts to get this to work. Yes, it did overwrite the cell:

[COLOR=#333333] tbl.Range("C" & Rows.Count).End(xlUp) = TabC[/COLOR]

This is the code that is still here:

[COLOR=#333333]If Coos.Cells(m, 1) = MBs.Cells(g, 13).Value And MBs.Cells(g, 12) Like "5*" Then[/COLOR]

After the check for the entry already being in the QA_Data table.
If the entry is not found in the table, now compare 2 worksheets for a common ID: Coos(Batch ID) and MBs(Order ID).
If this is true then look on that row on MBs worksheet and match the Order Doc# that starts with 5.
If true then add to the row entry data from both worksheets as defined.

Does that help?

DThib

 

[NoSparks]

have you also removed these ?

                g = MBRow
                m = CoRow

 

[DThib]

These are shorthand for the MBRow and CoRow information. I can remove them, but what would be the issue with them?

I have commented them out with the same effect as before.

DThib

 

[NoSparks]

If you leave them in does this make any sense

                g = MBRow
                m = CoRow
                 For m = 1 To CoRow
                  For g = 1 To MBRow

Run your code using the F8 key and follow along one line at a time to see what the code is doing and holding the cursor over the variables will reveal their values.

 

[DThib]

either way it is looking at the values already present in the table and then finding the same values to place in the table since these are the only ones matching at this point.

I need to not have the values duplicated in the table (QA_Table)

 

[NoSparks]

can you mock up a dummy workbook that is indicative of what you're working with and share it so as to see what is actually going on ?