Hyperlink coding in VBA

[Guest]

I want to create a hyperlink to a cell I hae just put some updated text in,
here is my code...
it runs down the first collumn looking to match a name, when found then puts
the values in the cells. All fine, but now I want to add a hyperlink to that
cell.
This code is within a WITH block for the destination sheet.

ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _
Address:=.Cells(yds, xds), TextToDisplay:=name

I ended up with the link looking for file called EX0009 which is actually
the contents of cell .Cells(yds,xds)
What I actually want is a link to this cell.
I may create hundreds of such links.
TIA

 

[Guest]

Graham,

Take a look at this sample code and you can adjust according to your needs.
This will create a hyperlink in A1 of the activesheet that will take you to
A1 of Sheet2.

With ActiveSheet
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="",
SubAddress:="Sheet2!A1", TextToDisplay:="Click Me!"
End With

 

[Guest]

Thanks
Am I going to have to convert my .cells() to a string value for the address?
Is there an easy way to do that? In the past I have used...

a = Asc("@")
b = Chr(a + (x \ 26)) & Chr(a + (x Mod 26))
where x was my column number.

....to get the letters of the colum part of the address.
and then concatenated this to the rest of the address.

 

[Guest]

You can use the Address property:

..Cells(yds, xds).Address

To make it include the workbook and sheet name, you can use:

..Cells(yds,xds).address(External:=True)

 

[Guest]

I had tried using .Address, but failed. The cell is on a different worksheet
is in the same file. just using .Cells(yds,xds).Address created a link the
right cell but on the same sheet as the hyperlink, not quite what I was after.

even using SubAddress:=Sheets("Detail Sheet").Cells(yds, xds).Address points
to a cell on the "Changes" sheet wher ethe hyperlink is.

Using External:=True works, but it seems a bit excessive to make it think it
is an external link.

Thanks, for the help and quick responses.

 

[Guest]

For your purpose, you'll need to set External=True. If you don't like doing
it that way, you can try:

SubAddress:= "'" & .Name & "'!" & .Cells(yds, xds).Address

or like this

SubAddress:="'Detail Sheet'!" & .Cells(yds, xds).Address

 

[Guest]

Thank you,

For your purpose, you'll need to set External=True. If you don't like doing
it that way, you can try:

SubAddress:= "'" & .Name & "'!" & .Cells(yds, xds).Address

or like this

SubAddress:="'Detail Sheet'!" & .Cells(yds, xds).Address