VBA identified Tables to compare values and skip if match

DThib

Active Member
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:
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
 
Last edited:

NoSparks

Well-known Member
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
Code:
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

Active Member
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?
 
Last edited:

NoSparks

Well-known Member
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"
Code:
            tbl.Range("C" & Rows.Count).End(xlUp) = TabC
What are your two inner loops supposed to be doing ?

What does this line determine ?
Code:
If Coos.Cells(m, 1) = MBs.Cells(g, 13).Value And MBs.Cells(g, 12) Like "5*" Then
 

DThib

Active Member
Hi,

I have removed this, it was one of my attempts to get this to work. Yes, it did overwrite the cell:
Code:
[COLOR=#333333] tbl.Range("C" & Rows.Count).End(xlUp) = TabC[/COLOR]
This is the code that is still here:
Code:
[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
 
Last edited:

DThib

Active Member
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
 
Last edited:

NoSparks

Well-known Member
If you leave them in does this make any sense
Code:
                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

Active Member
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

Well-known Member
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 ?
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top