Using HYPERLINK formula in VBA

[Gauraw]

Hi All,

I am having two columns - First column has Link (Url) and 2nd Column has Friendly Name.Using HYPERLINK formula,
I am able to create the Hyperlink in excel but to make it dynamic, I need to use VBA Code.

Kindly let me know how I can use the HyperLink formula - "HYPERLINK(I9,H9) " in VBA Code to achieve below Output.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Url[/TD]
[TD]Name[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]www.google.com[/TD]
[TD]Google[/TD]
[TD]Google[/TD]
[/TR]
[TR]
[TD]www.yahoo.com[/TD]
[TD]Yahoo[/TD]
[TD]Yahoo[/TD]
[/TR]
[TR]
[TD]www.mrexcel.com[/TD]
[TD]MrExcel[/TD]
[TD]MrExcel[/TD]
[/TR]
</tbody>[/TABLE]

Please note the above table list is dynamic. Based on the filters selection, the urls and no . of urls can change.

With Regards,
Gauraw

 

[tygrrboi]

What do you mean Dynamic?

I have URLs in column A. friendly names in column B and in C1 I type =HYPERLINK(A1,B1) and drag it down....

What result are you looking for?

 

[Gauraw]

Thanks tygrrboi for ur response.

The Dynamic means these urls are getting populated based on Combo box selection for one entity. When different entity is selected from the combo box different set of Urls (in terms of names & Count) will be populated. Thus, I need to put the Hyperlink task into VBA Code and run whenever change event happens in a combo box.

Hope this makes things clear.

What do you mean Dynamic?

I have URLs in column A. friendly names in column B and in C1 I type =HYPERLINK(A1,B1) and drag it down....

What result are you looking for?

 

[HockeyDiablo]

Sub urldo()
Dim urltext As String


Range("g2").Select

Do Until ActiveCell.Value = ""

urltext = ActiveCell.Offset(0, 0).Value

urltext = Right(urltext, Len(urltext) - 4)


urltext = Left(urltext, Len(urltext) - 4)

ActiveCell.Offset(0, 2).Value = urltext
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Give me a bit to see if I can get that to change the 3rd row to hyperlink.

 

[Gauraw]

Thanks HockeyDiablo for the response.

Just for more clarity and making things simple. The URL and Name column will be always there.
Its just we need to reference these cells and come up the Hyperlink using VBA.

Sub urldo()
Dim urltext As String


Range("g2").Select

Do Until ActiveCell.Value = ""

urltext = ActiveCell.Offset(0, 0).Value

urltext = Right(urltext, Len(urltext) - 4)


urltext = Left(urltext, Len(urltext) - 4)

ActiveCell.Offset(0, 2).Value = urltext
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Give me a bit to see if I can get that to change the 3rd row to hyperlink.

 

[HockeyDiablo]

Sub urldo()
Dim urltext As String
Dim newlink As String


Range("a2").Select

Do Until ActiveCell.Value = ""

urltext = ActiveCell.Offset(0, 0).Value

urltext = Right(urltext, Len(urltext) - 4)


urltext = Left(urltext, Len(urltext) - 4)

ActiveCell.Offset(0, 2).Value = urltext
If ActiveCell.Hyperlinks.Count = 1 Then
newlink = ActiveCell.Hyperlinks(1).Address
ActiveCell.Offset(0, 2).Hyperlinks.Add anchor:=ActiveCell.Offset(0, 2), Address:=ActiveCell.Offset(0, 2)
ActiveCell.Offset(0, 2).Hyperlinks(1).Address = newlink
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

I changed the 3 columns to A,B,C for testing. The first row works, now I need to figure out how to get the others.

 

[HockeyDiablo]

Sub urldo()
Dim urltext As String
Dim newlink As String
Dim r As Range
Dim x As Range


Set r = Range("A2").End(xlDown)
For Each x In Range("A2", r)


urltext = x

If x.Hyperlinks.Count = 1 Then
newlink = x.Hyperlinks(1).Address
x.Offset(0, 2).Hyperlinks.Add anchor:=x.Offset(0, 2), Address:=x.Offset(0, 2)
x.Offset(0, 2).Hyperlinks(1).Address = newlink
End If

urltext = Right(urltext, Len(urltext) - 4)


urltext = Left(urltext, Len(urltext) - 4)

x.Offset(0, 2).Value = urltext
x.Offset(1, 0).Select

Next x



End Sub

Finished....

 

[Gauraw]

Thank you HockeyDiablo for suggesting this piece of code. I used it as reference and created one as per my need.

Here is the code which I used for my tool :

[/COLOR]Sub AddHyperlink()


Dim i As Long
Dim Url As Long
Dim ws As Worksheet
Set ws = ActiveSheet


Url = Range("B" & Rows.Count).End(xlUp).Row


For i = 9 To Url 'set 9 to starting row


    ws.Hyperlinks.Add Range("E" & i), Cells(i, 9).Value, _
      , "Go to Requested site.", Cells(i, 8).Value
Next i


End Sub

Thanks Again !

 

[bobneurone]

Hi All,

I am having two columns - First column has Link (Url) and 2nd Column has Friendly Name.Using HYPERLINK formula,
I am able to create the Hyperlink in excel but to make it dynamic, I need to use VBA Code.

Kindly let me know how I can use the HyperLink formula - "HYPERLINK(I9,H9) " in VBA Code to achieve below Output.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Url[/TD]
[TD]Name[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]www.google.com[/TD]
[TD]Google[/TD]
[TD]Google[/TD]
[/TR]
[TR]
[TD]www.yahoo.com[/TD]
[TD]Yahoo[/TD]
[TD]Yahoo[/TD]
[/TR]
[TR]
[TD]www.mrexcel.com[/TD]
[TD]MrExcel[/TD]
[TD]MrExcel[/TD]
[/TR]
</tbody>[/TABLE]

Please note the above table list is dynamic. Based on the filters selection, the urls and no . of urls can change.

With Regards,
Gauraw

Before I applied the Hyperlinks.Add function which works well, I had naïvely tried the following code to insert a HYPERLINK formula in a cell from VBA but it does not work.

Sub testHL()
​

'Not working thus useless routine

myUrl = "www.google.com"
myName = "Google"
ActiveCell.Formula = "=HYPERLINK(""" & myUrl & """;""" & myName & """)"​

End Sub​