VBA identified Tables to compare values and skip if match

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.


Well-known Member
Mar 15, 2013
Office Version
Does this work for you ?
Private Sub Move_CB_Click()
' revision_2 August 3, 2019
    Dim QAWs As Worksheet, QAr As Range, CKDate As Date
    Dim oLo As ListObject, oNewRow As ListRow
Application.ScreenUpdating = False

CKDate = Sheets("Released Product").Range("K2").Value
Set QAWs = Sheets("QA_Data")
Set QAr = QAWs.ListObjects("QA_Table").ListColumns(3).DataBodyRange
Set oLo = Sheets("Released Product").ListObjects("RP_Table")
'add a row if the table has no rows
If oLo.DataBodyRange Is Nothing Then oLo.ListRows.Add

'loop thru QAr
For Each cel In QAr
    'check the date and col H status
    If cel.Offset(, 7) = CKDate And cel.Offset(, 5) = 2 Then
        'check if already in RP_Table
        With oLo
            If WorksheetFunction.CountIfs(.ListColumns(4).DataBodyRange, cel.Value, .ListColumns(1).DataBodyRange, CKDate) = 0 Then
                'need to add to RP_Table
                Set oNewRow = .ListRows.Add
                With oNewRow
                    .Range(1, 1) = CKDate
                    .Range(1, 2).Resize(, 6).Value = QAWs.Cells(cel.Row, 1).Resize(, 6).Value
                End With
            End If
        End With
    End If
Next cel

'remove first table row if it's blank    'uncomment if you want this
'If WorksheetFunction.CountA(oLo.ListRows(1).Range) = 0 Then oLo.ListRows(1).Delete

Sheets("Released Product").Activate

Application.ScreenUpdating = True
End Sub

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...