Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find
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 -- usadrea ----------------------------------------------------------------------- usadream's Profile: http://www.excelforum.com/member.php...fo&userid=3270 View this thread: http://www.excelforum.com/showthread.php?threadid=52577 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find
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 -- usadream ------------------------------------------------------------------------ usadream's Profile: http://www.excelforum.com/member.php...o&userid=32703 View this thread: http://www.excelforum.com/showthread...hreadid=525776 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |