How to create VBA code to go down a column and check if values are present in a table
Category: [Excel] Demo Available
How to create VBA code to go down a column and check if values are present in a table
Requirement:
The user needs 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.
The user 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:F11example1.xlsm
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by sktneer.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to count cells in a dynamic range matching two criteria given in table headers |
| How to create an Excel Pivot Table calculated field |
| How to use advanced lookup: Multiple criteria when looking up values in a table |
| How to group numbers in a pivot table |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.