Hi all,

I'm trying to find the range of a cell that contains the string <tea
member name> , output this range and have that content replaced wit
the content of a cell in another workbook.
i have a sub that looks like this:

Code
-------------------
   Dim colNum As Range, destrange As Range
 Dim searchRng, startRng As Range
 Dim tempTeamName As String
 Set searchRng = activeSheet.Range("D6:T6")
 Set startRng = activeSheet.Range("D6")
 tempTeamName = "<team member name>"
 
 Set colNum = searchRng.Find(What:=tempTeamName, _
 After:=startRng, _
 Lookat:=xlWhole, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByColumns, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False)
 On Error GoTo 0
 
 If Not colNum Is Nothing Then
 Set destrange = activeSheet.Cells("6", colNum)
 Else
 MsgBox ("Sorry coudln't find an empty team name")
 Exit Sub
 End I
-------------------

The find actually finds the cell, but it doesnt output the range
instead it output the search string (<team member name>). does anyon
know what i'm missing here?
I'd like the destrang variable to get the range from the cell that th
search string was found.
Can anyone help me? would be awesome!!

thanx in advance
thoma