Excel 2003 - VBA - Determining location of activecell

  • Thread starter Thread starter Craig Brandt
  • Start date Start date
C

Craig Brandt

I am running into the preverbial wall. I keep reaching a dead end when I try
to figure out how to locate the address of a cell found during a search.
I'm fairly sure it can be done, because I think I have used it in the past,
but searching for its use has been fruitless.
Is there something that I can wrap around the following statement that will
give me the column number? If so, what?

Range(Cells(5, 7), Cells(5, 251)).Find("SAM501").Select

The answer to this quest will permit me to finish this task and would be
much appreciated.

Thanks,
Craig
 
This will fail if "sam501" isn't found.

I think that this is better:

Dim FoundCell as range
with activesheet
set foundcell = .range(.cells(5,7),.cells(5,251)).find("sam501")
end with

if foundcell is nothing then
msgbox "not found"
else
foundcell.select 'not usually necessary
msgbox foundcell.column
end if

=====
Ps. It's usually much better to specify all the parms in that .find statement.
If you don't specify them, then your find will inherit whatever was last
used--in code or by the user!
 
Thanks Guys:

I would sware on a bible that I tried your(Don's) line of code and it didn't
work for me. Something else must not have been aligned exactly right and it
failed.

This time it worked. The problem may have been the find, since Dave points
out a big problem with the find holding on to the last application's
options.

Again, a big thank you, to both Don and Dave, for the assist,

Craig
 
Back
Top