PDA

View Full Version : VBA Find



Danny_Kemp
07-02-2015, 02:35 AM
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
07-02-2015, 03:55 AM
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
07-02-2015, 04:56 AM
Add

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

Danny_Kemp
07-03-2015, 12:36 AM
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
07-03-2015, 12:41 AM
Insufficient information in request results in insufficient responses.

Danny_Kemp
07-03-2015, 12:56 AM
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.