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


(Kinne) wrote:

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