Forum Discussion
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:// |
It's giving me a RunTime error 1004:
Sub addHyperlinks()
Dim i As Long
Dim va
Application.ScreenUpdating = False
va = Range("A5:G" & Cells(Rows.Count, "B").End(xlUp).Row)
For i = 1 To UBound(va, 2)
If Cells(i, 2) <> "" Then
Debug.Print Cells(i, "A")
Debug.Print Cells(i, 7)
Debug.Print Cells(i, 2)
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, "A"), _
Address:=va(i, 7), TextToDisplay:=va(i, 2)
End If
Next
Application.ScreenUpdating = True
End Sub
Thank you!
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.
8 Replies
1) Ubound(va, 2) is the number of columns in the range, but you use it to loop through the rows.
2) The range begins in row 5, but Cells(i, ...) begins in row 1
What exactly are you trying to accomplish?
- 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.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
