VBA identified Tables to compare values and skip if match

DThib

Active Member
Joined
Mar 19, 2010
Messages
464
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
have you also removed these ?
Code:
                g = MBRow
                m = CoRow
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top