colNum is a range not a number. It is the cell where you found the item. If
you want you could set destrange = colNum but I am not entirely sure why you
would. The reason that you believe colNum is the string that you were
searching for is that the default property of a range object is it's value,
so unless you specify otherwise colNum will return the value of the cell it
represents.

--
HTH...

Jim Thomlinson


"usadream" wrote:


Hi all,

I'm trying to find the range of a cell that contains the string <team
member name , output this range and have that content replaced with
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 If
--------------------


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

thanx in advance
thomas


--
usadream
------------------------------------------------------------------------
usadream's Profile: http://www.excelforum.com/member.php...o&userid=32703
View this thread: http://www.excelforum.com/showthread...hreadid=525776