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

  • 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!

  • 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.

  • 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!

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!