CODEWITHSUNDEEP
vba
Thomas Hain
Thomas Hain

Reputation: 21

automated hyperlink

I am trying to create a Macro which would create an hyperlink based on the content of an active cell.

Let's assume that in cell A1, there is a reference to cell B29 (i.e. "=B29"),

If my active cell is A1, I would like "=B29" to be replaced by "=HYPERLINK("#"&CELL("address",B29),B29)" when I run the Macro.

I want this macro to work only with the active cell. Is this possible?

I woudld appreciate if you could help me.

I tried the following but it is not working:

Sub hyperlinktocell()
    Dim num1 As Integer
    Dim celladress As String

    num1 = Len(ActiveCell.Formula) - 1
    celladdress = Right(ActiveCell.Formula, num1)
    ActiveCell.Formula = "=HYPERLINK(""#""&CELL(""address"",celladdress,celladdress)"
End Sub

I am new to VBA and spent almost 2 hours to get the above right without any success.

Upvotes: 2

Views: 543

Answers (1)

ApplePie
ApplePie

Reputation: 8942

Since you've done almost all the work, I'll post you what I had:

Private Sub test()
    Dim s As String
    s = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1)
    ActiveCell.Formula = "=HYPERLINK(""#""&CELL(""address""," & s & ")," & s & ")"
End Sub

As you can see, apart from using one less variable we have the same solution. Notice on the last line how I concatenate the string. In your code you have all those variables inside the double quotes, which makes them text. Then Excel complains that this isn't a valid value for this property which is right.

Upvotes: 1

Related Questions