CODEWITHSUNDEEP
vbaexcelhyperlink
Royce Quintana
Royce Quintana

Reputation: 11

Excel VBA: Error 1004 When Trying To Add Hyperlink

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

Upvotes: 1

Views: 1897

Answers (2)

DecoMartins
DecoMartins

Reputation: 131

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.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

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.

Upvotes: 2

Related Questions