help with find method

[grinning_crow]

I'm trying to return the actual range of the cell found using the fin
method, not the value in the cell found.

I recorded the action first and it gave this:

Columns("Y:Y").Select
Selection.find(What:="01/02/2004", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows
SearchDirection:=xlNext, _
MatchCase:=False).Activate


Now I need the "What" parameter to be dependent on the value of a cell
so I changed it to this:

Columns("Y:Y").Select
Selection.find(What:=sheet1.cells(2,1), After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows
SearchDirection:=xlNext, _
MatchCase:=False).Activate

I need the actual RANGE of the cell where the first instance of th
value in sheet1.cells(2,1) to be returned so I can then get the Ro
number to determine where in my list I want to begin my loop (to sav
trawling senselessly through 15000 rows) so I put something like this:

Set MyCell
sheet2.columns(25).find(sheet1.cells(2,1),ActiveCell,xlFormulas).Activate

Without Activate on the end, it just returns the value of the cell, bu
with Activate it errors as Object/With Block var not set etc.

Can anyone tell me how to return the range from the cell found?

Any help would be appreciated... thanks

 

[Tanner Dhesi]

Hi - try using ".Range" on the end instead of ".activate"
this should return the range of the selected cell.
'--------------------------------------
Dim myRange As Range

Columns("Y:Y").Select
myRange = Selection.Find(What:="01/02/2004", _
After:=ActiveCell,LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Range
'--------------------------------------
Hope this helps!

Tanner Dhesi
(e-mail address removed)

 

[Guest]

The previous code will not work because it's a range you're returning, you need to precede the variable with the "set" statement.

If you just want the row number, instead of specifying .Range (which is actually the default property, you were getting a value because you weren't using "Set"), you can specify .Row and the function will return an integer/long value.

Try:

Columns("Y:Y").Select
intFoundRow = Selection.Find(What:="01/02/2004", _
After:=ActiveCell,LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row

-Brad