Why is adding hyperlinks using VBA failing?

[asantos2021]

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!

[#2]

@asantos2021 

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?

[#3]

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.

[#4]

@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

[#5]

@Hans Vogelaar Thanks a lot, but I still get the same error:

[#6]

@asantos2021 

Could you attach a sample workbook that demonstrates the problem (without sensitive data)?

[#7]

@Hans Vogelaar of course. 

[#8 · Accepted solution]

@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.

[#9]

I didn't think we'd stumble upon this. Appreciate your time and the contributions you've made to the community through the years! All the best! Dank y wel.