Thanx again Tom.
problem with offset(0.-1) is street usually more than 1 row up.
A B
..
..
414 N. Main St
415 3732
416 3738
417 3744
etc...
How to get from B417 to A414

On Wed, 27 Aug 2003 08:53:22 -0400, "Tom Ogilvy"
wrote:

hlText(0) = ws.Name: hlText(1) = FindNum

FindNum is a range object, so hlText(1) will use the default property which
is value - so htText(1) will hold the value of the found cell (the address
you were looking for). I think you want
set hlText(1) = FindNum

Regardless, a range object isn't just a cell address - it is the cell and
the cell knows where it is located.


Address:="", SubAddress:=FindNum.Address(external:=True)

to illustrate from the immediate window:

set findnum = Activecell
? findnum.Address(external:=True)
[Book1]Sheet1!$E$28

I have found that anytime i need a string showing something like Sheet1!A1,
that using the external option with the address property gets the job done.

But to answer you question about the street name - if it is in column A

sStreetName = findnum.offset(0,-1).Value