Forum Discussion

asantos2021's avatar
asantos2021
Copper Contributor
Nov 30, 2021
Solved

Why is adding hyperlinks using VBA failing?

Hello,   Could you assist me in finding where the flaw is?   The data is as follows: A B C D E F G to Populate Ticker Data1 Data2 Data3 Data4 URL   AMD         https:/...
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Nov 30, 2021

    asantos2021 

    I see two serious problems:

    1) The URLs contain #. Excel cannot handle URLs that contain #.

    2) The URLs are extremely long - up to 4812 characters in length. I'm not sure Excel can handle that.

    If I try to convert the values in column G directly to hyperlinks, they don't work...

     

    I'm afraid I don't have a solution for you, sorry.

asantos2021's avatar
asantos2021
Copper Contributor
Nov 30, 2021
Hi!
I'm trying to get the address in col G, get the TextToDisplay in B and set it all in Col A, but after trying to understand UBound, apparently, I didn't get it all.
HansVogelaar's avatar
HansVogelaar
MVP
Nov 30, 2021

asantos2021 

Here you go:

Sub AddHyperlinks()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 5 To m
        ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & r), _
            Address:=Range("G" & r).Value, TextToDisplay:=Range("B" & r).Value
    Next r
    Application.ScreenUpdating = True
End Sub

Resources