following coding works find, however.....

Worksheets("a").select

Cells.Find(What:=zoek, After:=ActiveCell).Activate
rownr = ActiveCell.Row
colnr = ActiveCell.Column

if I don't use Worksheets("a").select and am located in a different sheet
e.g. worksheets("b").... it doesn't work. Is there a way to point to a
specific sheet from within the find command no matter in which worksheet you
are located at that time.

Hi

No need to select anything.
Look at this:

With Sheets("a")
Set f = .Cells.Find(what:=zoek, After:=ActiveCell)
If Not f Is Nothing Then
rownr = f.Row
colnr = f.Column
End If

Regards,
Per

On 20 Sep., 15:39, Luc wrote:
following coding works find, however.....

Worksheets("a").select

Cells.Find(What:=zoek, After:=ActiveCell).Activate
* *rownr = ActiveCell.Row
* *colnr = ActiveCell.Column

if I don't use Worksheets("a").select and am located in a different sheet
e.g. worksheets("b").... it doesn't work. *Is there a way to point to a
specific sheet from within the find command no matter in which worksheet you
are located at that time.

Thanks for the reply but doesn't really work wel.
zoek in the find statement is a variable and isn't being picked up!

Worksheets("a").select
zoek = "text"
Cells.Find(What:=zoek, After:=ActiveCell).Activate
rownr = ActiveCell.Row
colnr = ActiveCell.Column


any ideas?

I very rarely need to select an item beofre using it. I either include the
worksheet name in the instruction or use a with statement

with sheets("Sheet1")
set c = .Cells.Find(What:=zoek, After:=ActiveCell)
rownr = ActiveCell.Row
colnr = ActiveCell.Column

if c is nothing then
msgbox("Cannot Find : " & zoek)
else
c.activate
end with


or

set c = sheets("sheet1").Cells.Find(What:=zoek, After:=ActiveCell)
rownr = ActiveCell.Row
colnr = ActiveCell.Column

if c is nothing then
msgbox("Cannot Find : " & zoek)
else
c.activate

"Luc" wrote:

following coding works find, however.....

Worksheets("a").select

Cells.Find(What:=zoek, After:=ActiveCell).Activate
rownr = ActiveCell.Row
colnr = ActiveCell.Column

if I don't use Worksheets("a").select and am located in a different sheet
e.g. worksheets("b").... it doesn't work. Is there a way to point to a
specific sheet from within the find command no matter in which worksheet you
are located at that time.

I wouldn't use the activecell, either.

dim FoundCell as range
dim RowNr as long
dim ColNr as long
with worksheets("a")
set foundcell = .cells.find(what:=zoek,after:=.cells(.cells.count) ,....)
if foundcell is nothing then
'do nothing
else
rownr = foundcell.row
colnr = foundcell.column
end if
end with

ps. I would supply all the parms to that .find statement. If you don't, then
your .find will be inheriting the parms from the last find (xlwhole/xlpart,
matchcase, etc)--no matter if it was done in code or via the user interface.



Luc wrote:

following coding works find, however.....

Worksheets("a").select

Cells.Find(What:=zoek, After:=ActiveCell).Activate
rownr = ActiveCell.Row
colnr = ActiveCell.Column

if I don't use Worksheets("a").select and am located in a different sheet
e.g. worksheets("b").... it doesn't work. Is there a way to point to a
specific sheet from within the find command no matter in which worksheet you
are located at that time.

--

Dave Peterson