Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: VBA identified Tables to compare values and skip if match
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Location
    New England
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry VBA identified Tables to compare values and skip if match

    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
        Dim TBL1, TBL2, TabC As Range
    
    
        
        Set MBs = ThisWorkbook.Sheets("MB51_Draw")
        Set Coos = ThisWorkbook.Sheets("COOIS_Draw")
        Set QAws = ThisWorkbook.Sheets("QA_Data")
        
        Set tbl = Sheets("QA_Data") '.ListObjects("QA_Table")
        With tbl
          Set TBL1 = .Range("C2", .Range("C1").End(xlDown))
        End With
        Set searchw = Sheets("COOIS_Draw")
        With searchw
           Set TBL2 = .Range("A2", .Range("A1").End(xlDown))
        End With
    
          For Each TabC In TBL1
             If WorksheetFunction.CountIf(TBL2, TabC) = 0 Then
                tbl.Range("C" & Rows.Count).End(xlUp) = TabC
                
                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 by DThib; Jul 17th, 2019 at 12:34 PM.

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    814
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    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

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Location
    New England
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    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 by DThib; Jul 18th, 2019 at 09:03 AM.

  4. #4
    Board Regular
    Join Date
    Mar 2013
    Posts
    814
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    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

  5. #5
    Board Regular
    Join Date
    Mar 2010
    Location
    New England
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    Hi,

    I have removed this, it was one of my attempts to get this to work. Yes, it did overwrite the cell:
    Code:
     tbl.Range("C" & Rows.Count).End(xlUp) = TabC
    This is the code that is still here:
    Code:
    If Coos.Cells(m, 1) = MBs.Cells(g, 13).Value And MBs.Cells(g, 12) Like "5*" Then
    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 by DThib; Jul 18th, 2019 at 10:59 AM.

  6. #6
    Board Regular
    Join Date
    Mar 2013
    Posts
    814
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    have you also removed these ?
    Code:
                    g = MBRow
                    m = CoRow

  7. #7
    Board Regular
    Join Date
    Mar 2010
    Location
    New England
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    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 by DThib; Jul 18th, 2019 at 12:04 PM.

  8. #8
    Board Regular
    Join Date
    Mar 2013
    Posts
    814
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    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.

  9. #9
    Board Regular
    Join Date
    Mar 2010
    Location
    New England
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    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)

  10. #10
    Board Regular
    Join Date
    Mar 2013
    Posts
    814
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA identified Tables to compare values and skip if match

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •