Go down a column and check if values are present in a table

0zero0 · Jun 20th 2017

Please see attached workbook.

I need a macro to check each name in range F2:F11 and determine if it exists in MainTable, column 1.

If it does, do nothing. If it does not, call a MsgBox.

I have tried a bunch of different methods, but I have the same problem with each one: the MsgBox is called after every instance of the value from range F2:F11 not existing in MainTable; i.e. it checks the cells one by one and calls the MsgBox each time the value is not present.

I would like the code to run through the entire table and then, at the end, call the MsgBox if the name from range F2:F11 was not found.

Said another way: Run through MainTable and check for the name "Bryan". If present, do nothing and move on to "Darrel". If not, call MsgBox. And do that for each name in range F2:F11forum.ozgrid.com/index.php?attachment/72780/

sktneer · Jun 20th 2017

Re: Go down a column and check if values are present in a table

Does this work for you?
In the attached, click the button "Check Names" to run the code.

Code

Sub CheckNames()
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range, cell As Range
Dim str As String


Set ws = Sheets("Sheet1")
Set tbl = ws.ListObjects("MainTable")
Set rng = Range("F2:F11")
str = "The following names are not present in Main Table." & vbNewLine & vbNewLine
For Each cell In rng
    If Application.CountIf(tbl.Range.Columns(1), cell) = 0 Then
        str = str & cell.Value & vbNewLine
    End If
Next cell
If str <> "" Then
    MsgBox str, vbExclamation
Else
    MsgBox "All the names were found in Main Table.", vbInformation
End If
End Sub

Display More

0zero0 · Jun 20th 2017

Re: Go down a column and check if values are present in a table

Wow, thank you for the quick response! Ideally, a dialogue would put up for name that is not present. i.e. "Bryan is not in the master list". User clicks OK, then "Darrel is not in the master list" and so on.

Reason is, I have an "Add employee" button that will be called up in the instance a crewmember is not included in MainTable, which will allow the user to complete a userform and add that person into the Main table.

But, I think I can poke through the code you provided and make that happen.

Thanks again!

sktneer · Jun 20th 2017

Re: Go down a column and check if values are present in a table

You're welcome Ozero!

Please replace the existing code with the following one...

Code

Sub CheckNames()
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range, cell As Range
Dim str As String


Set ws = Sheets("Sheet1")
Set tbl = ws.ListObjects("MainTable")
Set rng = Range("F2:F11")
str = "The following name is not present in Main Table." & vbNewLine & vbNewLine
For Each cell In rng
    If Application.CountIf(tbl.Range.Columns(1), cell) = 0 Then
        str = str & cell.Value
        MsgBox str
        Exit Sub
    End If
Next cell
End Sub

Display More

0zero0 · Jun 22nd 2017

Re: Go down a column and check if values are present in a table

Works great! Thanks again

sktneer · Jun 22nd 2017

Re: Go down a column and check if values are present in a table

You're welcome again

Go down a column and check if values are present in a table

Participate now!

Share