Find value not on activesheet

[tiredofit]

Is it possible to find a value that is not on the active sheet?

Sheet1.Cells.Find(What:="somevalue", _
                       After:=ActiveCell, _
                       LookIn:=xlFormulas, _
                       LookAt:=xlPart, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False, _
                       SearchFormat:=False)

If Sheet1 is the activesheet and contains the value, "somevalue" the above will run.

However if Sheet2 is the activesheet, the above will crash.

How can I amend the code so it will work without having to select Sheet1 first?

Thanks

 

[Fluff]

You will need to remove the "ActiveCell" reference.

 

[tiredofit]

You will need to remove the "ActiveCell" reference.

You mean like this:

Sheet1.Cells.Find(What:="somevalue", _
                       LookIn:=xlFormulas, _
                       LookAt:=xlPart, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False, _
                       SearchFormat:=False)

 

[Fluff]

Did you try it?

 

[tiredofit]

Did you try it?

yes, it didn't work.

error message:

Run-time error '1004'

Activate method of Range class failed

However I amended it to:

Dim abc
    
abc = Sheet1.Cells.Find(What:="somevalue", _
                       LookIn:=xlFormulas, _
                       LookAt:=xlPart, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False, _
                       SearchFormat:=False)

and this did return abc as somevalue (if it existed). If it did not, it crashed and I expected that.

Thanks for your help.

 

[Fluff]

In that case you did not post the entire code.
You cannot activate a cell unless it's on the active sheet.

 

[tiredofit]

In that case you did not post the entire code.
You cannot activate a cell unless it's on the active sheet.

Thanks

 

[Fluff]

My pleasure.