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



--
Regards,
Tom Ogilvy



"Jay Fincannon" wrote in message
...
Tom
I like the "Dummy" one best, thanks
Another problem.
I have a workbook with 111 worksheets representing route books.
Column A has street names then column B contains the customer address
numbers for that street.
After using the VBA Find method to locate an address number how can
you back up to get the street name. Here's a piece of my code.
everything is Dim'd properly.

Sub FindRoute_Num(Num As String)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "LookMeUp" Then

Set uRange = ws.UsedRange
Set FindNum = uRange.Find(Num, , , Look)

If Not FindNum Is Nothing Then
FirstHit = FindNum.Address
' I would like to backup here and get the street name
and add it to hlText array
Do
hlTarget = FindNum.Address
hlText(0) = ws.Name: hlText(1) = FindNum
Sheets("LookMeUp").Hyperlinks.Add _
Anchor:=Cells(r, 1), _
Address:="", SubAddress:=ws.Name & "!" & hlTarget, _
TextToDisplay:=Join(hlText, " ")
r = r + 1
Found = Found + 1
Set FindNum = uRange.FindNext(FindNum)
Loop While Not FindNum Is Nothing And FindNum.Address < _
FirstHit
End If
End If
Next



On Tue, 26 Aug 2003 13:23:45 -0400, "Tom Ogilvy"
wrote:

It should have been subaddress without the external:=true - went
braindead
for a while.

but that didn't work in my test - gave me another type of error - as
suggested, I think it is fighting the event. Anyway, this worked:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.OnTime Now, "Dummy"
End Sub


now create, in a general module, a sub named dummy

Sub Dummy()
CenterOnCell ActiveCell
End Sub


An alternative would be to use the beforedoubleclick event or even
selection
change for a cell and skip the hyperlink.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$E$5" Then
Cancel = True
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$E$6" Then
CenterOnCell Worksheets("Sheet3").Range("Z35")
End If
End Sub

use one or the other.
You could format the cell to look like a hyperlink if you are using
selection change or for beforedoubleclick, put in text like

doubleclick to go to Z35