find a value in another sheet

  • Thread starter Thread starter Luc
  • Start date Start date
L

Luc

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
 
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
 
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.
 
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?
 
Back
Top