Syntax to obtain the SubAddress of a Hyperlink in A1 on Sheet1

D

Dennis

2003

In addition, the best way to separate the Sheetname from the Cell
reference from that SubAddress.

Any thoughts appreciated!

TIA EagleOne
 
B

Bob Phillips

With Range("A1").Hyperlinks.Item(1)
MsgBox Left(.SubAddress, InStr(.SubAddress, "!") - 1)
MsgBox Right(.SubAddress, Len(.SubAddress) - InStr(.SubAddress,
"!"))
End With


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
D

Dennis

Bob,

This form of Hyperlink is volitile:
=HYPERLINK("#"&CELL("address",'Sheet2'!B32),"Click Me")

Above is also the starting-point formula in Cell A1 on Sheet1.

I inserted a row in Sheet2 (changing the linked Cell from B32 to B33)

Now the Formula in Cell A1 Sheet1 is:

=HYPERLINK("#"&CELL("address",'Sheet2'!B33),"Click Me")

This is exactly what I want .... except that:

When I run the Macro:

With Range("A1").Hyperlinks.Item(1)
MsgBox Left(.SubAddress, InStr(.SubAddress, "!") - 1)
MsgBox Right(.SubAddress, Len(.SubAddress) - _
InStr(.SubAddress, "!"))
End With

The macro returns:

Sheet2
B32 (Which s/b B33)

Is not the subaddress updated when a volitile formula changes?

Plan B

Do a "string" analysis of the current formula to get B33.

What are your thoughts and/or suggestions (to a better approach?)

TIA EagleOne
 
D

Dennis

Bob,

This is what I came up with:

With Range("A1")
BeginStr = Len(Left(.Formula, InStr(.Formula, "!") + 1))
SecondStr = InStr(BeginStr, .Formula, ")")
CellRef = Mid(.Formula, BeginStr, SecondStr - BeginStr)
MsgBox CellRef
End With

That said, I would like to know why the SubAddress information was not
updated permitting your macro to return the updated SubAddress, if in
fact, it ever is updated.

TIA EagleOne
 
B

Bob Phillips

I don't undedrstand why you are getting a result at all, the code looks at
the hyperlinks coolection, but by using that formula, you haven't created
one. It should fail.

However, to get whata you want, I think that you have to parse it

Dim sTmp As String
With Range("A1")
sTmp = Replace(Mid(.Formula, InStr(.Formula, "CELL("), InStr(.Formula,
")") - InStr(.Formula, "(") - 5), _
"CELL(""address"",", "")
MsgBox Left(sTmp, InStr(sTmp, "!") - 1)
MsgBox Right(sTmp, Len(sTmp) - InStr(sTmp, "!"))
End With



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
D

Dennis

Looks like our last posts were almost simultanious.

Thank you for your help and knowledge. As you see, I really do try to
do my own
work and not burden others with my work. That said, you and the others
are a gift to the learning process.

EagleOne
 
D

Dennis

The "reason" that I did get a result was this:
I replaced a pre-existing Hyperlink formula (only) with the volitile
version. I hit return and did not realize that the hyperlink
information was still in the collection.

Therefore, I went back and removed the link and the world is back in
balance.

EagleOne
 
B

Bob Phillips

I'm glad about that <g>. The world was definitely a-kilter for a moment.

Bob
 
Top