find a value in another sheet
 

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.

find a value in another sheet
 

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.

find a value in another sheet
 

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.

find a value in another sheet
 

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

find a value in another sheet
 

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?