VBA Find

[Danny_Kemp]

Hi,

My first post on this forum - so hi everyone!

I have some code that works really well, but I now want to add to it
and need some help!

Basically what my current code does is search an
excel database of employees, and returns the employees name, clock card ID and
their trade if 3 criteria are met (manager, boat line & team
name).

Now what I want to do is check that all employees in my excel database have been
"allocated to a team". So broadly speaking the code should take the value in B2
of the database, search in the tab where my teams have been populated:

do nothing if the it finds the value and then then search on the next value (B3, then B4 etc)
if the employee name is not found I want it to paste this name into a column, so I can then have a list of names that have not been found.

Can someone help me with the code that would do this?

Thanks

[Bob Phillips]

Public Sub FindMatches()
Dim sh As Worksheet
Dim rng As Range
Dim lastrow As Long
Dim i As Long

    With ActiveSheet
    
        lastrow = .Range("B2").End(xlDown).Row
        .Range("C2").Resize(lastrow - 1).FormulaR1C1 = "=MATCH(RC[-1],Employees!C[-2],0)"
        .Range("C1").Value = "Match"
        Set rng = .Range("C1").Resize(lastrow)
        rng.AutoFilter Field:=1, Criteria1:="#N/A"
        On Error Resume Next
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng Is Nothing Then
        
            Set sh = Worksheets.Add
            sh.Name = "Mismatches"
            rng.Offset(0, -1).Copy sh.Range("A1")
        End If
        
        .Columns("C").Delete
    End With
End Sub

[SamT]

Add

With ActiveSheet ' <-- Add next line after this line
 Columns(3).Insert

[Danny_Kemp]

Hi,

Thanks for your replies. However, due to to the way the templates are set out it is not as simple as copying a list of employees names in and doing a match - this would have been the best way, but life is not that kind!

I have found a way of searching each employee mentioned in the database and then putting their details in another sheet if it can not find them in any of the team lists.

Thanks again for your suggestions

[SamT]

Insufficient information in request results in insufficient responses.

[Danny_Kemp]

Insufficient information in request results in insufficient responses.

I feel I did provide information that the whole tab needed to be searched when stating: the code should take the value in B2
of the database, search in the tab where my teams have been populated.