Hyperlink formulas in VBA code - one is working and one is not

phrankndonna · Apr 3rd 2021

Hi. I've got the small module shown below that I'm working on to add a FedEx or UPS hyperlink to a list of tracking numbers. The FedEx formula works just fine, but the UPS formula is bugging out. From my perspective, the format is identical, but I'm obviously missing something. Can anyone suggest what might be out of place, please? Thank you!

Code

Sub testing()
 
    Dim TrackingNum As String
    Dim x As Integer
    Dim LastRow As Integer
    Dim LinkString As String
   
 
    LastRow = Range("A23").End(xlDown).Row
    For x = 24 To LastRow
        TrackingNum = Range("F" & x).Value
        'if tracking number is FedEx
        If Left(TrackingNum, 1) = "5" Then
            Range("F" & x).Formula = _
            "=HYPERLINK(""https://www.fedex.com/fedextrack/?trknbr=" & TrackingNum & "&trkqual=12021~" & TrackingNum & "~FDEG""," & TrackingNum & ")"
        Else
            'if tracking number is UPS
            If Left(TrackingNum, 1) = "1" Then
            Range("F" & x).Formula = _
                "=HYPERLINK(""https://wwwapps.ups.com/WebTracking/...Home&tracknum=" & TrackingNum & "&AgreeToTermsAndConditions=yes&track.x=17&track.y=4/trackdetails""," & TrackingNum & ")"
           End If
        End If
       
        Range("F" & x).Select
        With Selection
            .HorizontalAlignment = xlLeft
            .NumberFormat = "0"
            .Font.Name = "Cambria"
            .Font.Size = 12
            .Font.Color = -4165632
            .Font.Underline = xlUnderlineStyleNone
        End With
    Next
 
End Sub

Display More

rollis13 · Apr 3rd 2021

Just to add missing cross-post link: LINK and LINK

phrankndonna · Apr 3rd 2021

Sorry about that. There are obviously different users/viewers for each one, so is it best to post to one and then just post the link on the others, as you did? Or is it best to post both the link and the full text? Also looking to see how to show this as SOLVED.

Posting now to show the solution. The issue was the FedEx tracking number is a number and is apparently ok in the Hyperlink formula without the quotes for the friendly name. The UPS tracking number has letters, so it is a string, and it needs the quotes around the tracking number. So, since the full hyperlink formula wouldn't allow adding the extra quotes to the TrackingNum variable, the solution was to break the hyperlink formula in two parts and add the TrackingNum to another string variable, and then stich the two together in a separate variable that gets passed as the formula.

Code

Sub testing2()

Dim TrackingNum As String
Dim TrackingNum2 As String
Dim x As Integer
Dim LastRow As Integer
Dim LinkString As String
Dim LinkString2 As String

LastRow = Range("A23").End(xlDown).Row
For x = 24 To LastRow
    TrackingNum = Range("F" & x).Value

    If Left(TrackingNum, 1) = "5" Then
        Range("F" & x).Formula = _
                "=HYPERLINK(""https://www.fedex.com/fedextrack/?trknbr=” & TrackingNum & "&trkqual=12021~" & TrackingNum & "~FDEG""," & TrackingNum & ")"
    Else
        If Left(TrackingNum, 1) = "1" Then
           TrackingNum2 = """" & TrackingNum & """)"
           LinkString = "=HYPERLINK(""https://wwwapps.ups.com/WebTracking/processInputRequest?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&tracknum=" & TrackingNum & "&AgreeToTermsAndConditions=yes&track.x=17&track.y=4/trackdetails"","
           LinkString2 = LinkString & TrackingNum2
           Range("F" & x).Formula = LinkString2
        End If
    End If
    Range("F" & x).Select
    With Selection
         .HorizontalAlignment = xlLeft
         .NumberFormat = "0"
         .Font.Name = "Cambria"
         .Font.Size = 12
         .Font.Color = -4165632
         .Font.Underline = xlUnderlineStyleNone
    End With

Next

End Sub

Display More

phrankndonna · Apr 4th 2021

Hi again. I posted a solution above that my son and I came up with, but Rollis13 came up with the more direct and elegant solution within the hyperlink formula itself. I just wasn't using enough quotation marks around the friendly name variable to account for the UPS string. Just posting the best solution here too. Thank you!

Code

"=HYPERLINK(""https://wwwapps.ups.com/WebTracking/...Home&tracknum=" & TrackingNum & "&AgreeToTermsAndConditions=yes&track.x=17&track.y=4/trackdetails"",""" & TrackingNum & """)"

Hyperlink formulas in VBA code - one is working and one is not

Participate now!

Share