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

[phrankndonna]

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!


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/processInputRequest?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&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

[rollis13]

Looks like your UPS formula works only with all-number code. Try changing your UPS formula to this (added some more double-quotes at the end):
"=HYPERLINK(""https://wwwapps.ups.com/WebTracking/...Home&tracknum=" & TrackingNum & "&AgreeToTermsAndConditions=yes&track.x=17&track.y=4/trackdetails"",""" & TrackingNum & """)"


PS. just to add missing cross-post links: LINK (https://www.ozgrid.com/forum/index.php?thread/1229380-hyperlink-formulas-in-vba-code-one-is-working-and-one-is-not/&postID=1247549#post1247549) and LINK (https://www.excelforum.com/excel-programming-vba-macros/1345936-hyperlink-formulas-fedex-and-ups-one-works-and-the-other-does-not.html#post5497653)

[phrankndonna]

Sorry about just posting on the different sites. There are obviously different users/viewers for each one, ne 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. I posted this solution below to the Ozgid site, but your solution works and is much simpler. I knew the issue was with the friendly name in the hyperlink formula, but I couldn't figure out how to resolve it. I had tried double quotes after the comma, but not a third quote surrounding the & TrackingNum &. That was the ticket.

As I said, your solution is more desirable and elegant, but posting what my son and I came up with. The issue indeed is that the UPS tracking number is a string and needs the quotes around the tracking number in the hyperlink formula. So, our 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.

Thank you very much!!

[SamT]

Just post the links. It is courteous to post the Solution on the other sites. Thank you

Many people watch many sites.

You can mark our threads solved using the Thread Tools at the top of your threads.

[rollis13]

Glad having been of some help :thumb.