Excel VBA: Error 1004 When Trying To Add Hyperlink

Question

The series of commands seems to result in Runtime Error: 1004 I would like to know what the cause of this error is.

If I do not have the Activesheet.Hyperlinks.add line the cell values get set correctly, just missing the hyperlink... which would make me think I've lost the xCell reference but I've placed debug statements just before the hyperlink.add and it seems to be accessible.

Example URL: http://www.walmart.com/ip/Transformers-Robots-in-Disguise-3-Step-Changers-Optimus-Prime-Figure/185220368

    For Each xCell In Selection
    Url = xCell.Value
    If Url = "" Then
       'Do Nothing
    ElseIf IsEmpty(xCell) = True Then
       'Do Nothing
    ElseIf IsEmpty(Url) = False Then
        splitArr = Split(Url, "/")
        sku = splitArr(UBound(splitArr))
        xCell.Value = "https://www.brickseek.com/walmart-inventory-checker?sku=" & sku
        'Error happens on next command
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
    End If
Next xCell

Gary's Student · Accepted Answer · 2017-10-14 20:09:56Z

2

Don't both with .Value
Don't use .Formula:

Sub demo()
    Dim s As String, xCell As Range

    s = "http://www.walmart.com"
    Set xCell = Range("B9")
    ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=s, TextToDisplay:=s
End Sub

is a typical working example.

answered Oct 14, 2017 at 20:09

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Royce Quintana Over a year ago

Just to clarify, the issue with my sequence of commands is that I was setting the value of var xCell, and then trying to also make a modification with the add hyperlink to the same cell. By setting a variable "s" only, and only the calling the add hyperlink this stops the dereference of xCell that was being caused? Your solution worked I'm just not sure I know exactly why. Thank you.

DecoMartins · Accepted Answer · 2021-01-12 18:23:42Z

1

There is always another possibilty, that your sheet may be locked and you have to grant permission to do so when locking the sheet.

I know this is not the solution for the problem described here, but the non-deterministic error messages provided by Microsoft VBA is the same. I came here looking for the solution of my problem, an others might bump in this and find my comment relevant.

answered Jan 12, 2021 at 18:23

DecoMartins

1311 silver badge5 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Error 1004 When Trying To Add Hyperlink

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related