Find instruction fails (from foreign sheet)

  • Thread starter Thread starter Kinne
  • Start date Start date
K

Kinne

This code works when I call it from a button on the actual sheet that
has to be querried:Cells.Find(What:="search something", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Activate

This code doesn't work and produces an error "1004 - Active method of
Range class failed" when triggered from another sheet than the one
that has to be querried:Worksheets("Sheet1").Range("Database").Cells.Find(What:="search
something", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Activate

It must be because of "Worksheets("Sheet1").Range("Database")."... but
what is wrong???

Thanks for any help advice...

Kinne
 
The problem is the line

After:=ActiveCell, _

because ActiveCell refers to a cell not on the sheet you are
searching in. You can omit this line of code, or change it to a
cell reference in Worksheets("Sheet1").Range("Database").



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)


Kinne said:
This code works when I call it from a button on the actual sheet that
has to be querried:
Cells.Find(What:="search something", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Activate

This code doesn't work and produces an error "1004 - Active method of
Range class failed" when triggered from another sheet than the one
that has to be querried:
Worksheets("Sheet1").Range("Database").Cells.Find(What:="search
something", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Activate

It must be because of
Click to expand...
"Worksheets("Sheet1").Range("Database")."... but
 
You have 2 problems, both solved if you activate the sheet before you
search..

After:=ActiveCell <= If Sheet3 is active.. Then the activeCell will
also be on Sheet3... so your find won't work

You activate the cell found.. but you can't activate a cell without
activating it's worksheet first.

With following he'll start searching from the 1st cell in the database
and then Goto the cell (the last True means he'll scroll in such a way
the cell will be in TopLeft corner of screen.


Sub FindIt()

Dim rngFound As Range
With Worksheets("Sheet1").Range("Database")
Set rngFound = .Find( _
What:="search something", _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If rngFound Is Nothing Then Beep Else Application.Goto rngFound, True

End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Back
Top