excelvba

Append excel cell to end of a link and create a hyperlink

I currently have a spreadsheet that contains a column of Fedex tracking numbers.

The intended behavior I'm looking for is to take the raw tracking number from the cell (eg. 778542947360), and append it to the end of "https://www.fedex.com/wtrk/track/?tracknumbers=".

It would then be clickable in the cell with the text of JUST the tracking number (not the whole link).

I have been trying to accomplish this by setting the target.formula with a =HYPERLINK formula, but have been unable to change the "friendly name" to just the tracking number.

Solution

  • Sheet formula HYPERLINK should work for your case.

    Sub Demo()
    Dim rngData As Range
    Const URL = "https://www.fedex.com/wtrk/track/?tracknumbers="
    Set rngData = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    rngData.Offset(0, 1).FormulaR1C1 = _
        "=HYPERLINK(""" & URL & """&RC[-1],RC[-1])"
End Sub

    enter image description here

    If you prefer to create hyperlinks on column A

    Sub Demo2()
    Dim c As Range, rngData As Range
    Const URL = "https://www.fedex.com/wtrk/track/?tracknumbers="
    Set rngData = ActiveSheet.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each c In rngData.Cells
        ActiveSheet.Hyperlinks.Add Anchor:=c, Address:= URL & c.Value, _
        TextToDisplay:=CStr(c.Value)
    Next
End Sub

    enter image description here