Instead of using a find and replace you can just use a Vlookup formula. That
is by far the easiest way to attach the service to the name. If you are
ammenable to this then give that a whirl and put the service in the 8th
column. This will make things a whole pile easier. Once that is done this can
be accomplished either with code or with a pivot table (possibly). Let me
know what you think...

"Lindsey Martin" wrote:

Hi Jim

The service is not one of the columns on the Data sheet, however, on
another sheet TBR2, i have all the Team Managers listed for a find and
replace procedure that I use. This could be easily amended so that
column B is the service.

This is the code I use for the find and replace

Sub Find_Replace_TM()

Application.ScreenUpdating = False

Dim range1 As String
Dim bottomrow As Integer
bottomrow = ActiveSheet.Range("B65536").End(xlUp).Row

range1 = "B1:B" & bottomrow

Dim range2 As Range
Dim ws As Worksheet, fnd As Range, x%

Set range2 = ActiveSheet.Range(range1)
Set ws = Worksheets("TBR2") 'checks the fields to remove on Sheet2
Set fnd = ws.Range(ws.Range("A1"), ws.Range("A65536").End(xlUp))

For x = 1 To fnd.Cells.Count
range2.Replace What:=fnd(x), Replacement:="" 'replace with blanks
Next

'delete the now empty rows
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Application.ScreenUpdating = True

End Sub

I'm assuming this could be easily amended but as I am quite new to VBA
and I "borrowed" the above off the web and amended to fit my needs, I'm
a little stuck as to where to start

Any idea on this?

Cheers
Lindsey


*** Sent via Developersdex http://www.developersdex.com ***