find

[usadream]

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

 

[Guest]

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.

 

[usadream]

Hi,

thanx a lot for your reply.
I have two further question:
1. how can i set the variable of colnum so that when i call the
following sub the colnum variable will pass the range and not the
content of the cell that was found?

GetData FName(N), "Timetracking plain", "B10", colNum, False

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range,
HeaderRow As Boolean)

2.With the find i actually found the range of the cell containing the
value. How can i determine the column this cell was found in?

again, thanx a lot for your support

regards
thomas