Solved: Hyperlink Cell to Include Cell Value in Link

[Emoncada]

I don't know if this is possible, but I've seen some amazing things so this might be able to be done in vba.

I have UPS Tracking Numbers in a column and I would like to see if i can hyperlink the Cells with the Tracking Numbers and have the user click on it and automatically include it to this url. Tracking Numbers would be in a Range from E3: E500.

wwwapps.ups.com/WebTracking/track?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=(Here_the_cell_value_would_go)+&track.x=Track

This would automatically pull up UPS and track that package without the user needing to go and copy that cell then go to UPS and paste and Track.

Can this be done?

[ukdane]

Something like this?

[vba]
Dim Trackingnr as string

Trackingnr = ActiveCell.value

ActiveCell.Formula = "=HYPERLINK(""[wwwapps.ups.com/WebTracking/track? _
HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=("" & _
Trackingnr & "" +&track.x=Track"]"",""" & Trackingnr & """)"
[/vba]

Not tested.

[Emoncada]

That doesn't seem to work but I think your on the right track.

[ukdane]

I've just tested this, and it works.
Just run the code on the cell with the tracking nr.

[VBA]
Sub tracking()
Dim Trackingnr As String
Dim mytrace As String
Trackingnr = Trim(ActiveCell.Value)

mytrace = """http:\\wwwapps.ups.com/WebTracking/track?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=" & Trackingnr & "+&track.x=Track"""

ActiveCell.Formula = "=HYPERLINK(" & mytrace & ",""" & Trackingnr & """)"
End Sub
[/VBA]

[Emoncada]

Ok that seems to work only thing is, is there a way that I can have that macro hyperlink a range? so I don't have to go to each cell and run macro?

[ukdane]

Emoncada: Do you mean one hyperlink for an entire range, or one hyperlink per cell for each cell in the range?

Is the range set (always known; for example A1:A13), or is it variable (eg: A1:A?) ?

[Emoncada]

Yes one hyperlink per cell for each cell. Each cell will have a different tracking number unique to each row. So it would be like (D3 : D).

[ukdane]

[VBA]Sub tracking()
Dim Trackingnr As String
Dim mytrace As String
Dim mylastcell As Long
Dim activerow As Long


mylastcell = ActiveSheet.Range("A65536").End(xlUp).Row
activerow = 1 'replace 1 with the first row where the tracking number appears

Do While activerow < mylastcell + 1
Trackingnr = Trim(Range("A" & activerow).Value) 'replace "A" with the actual column
mytrace = """http:\\wwwapps.ups.com/WebTracking/track?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=" & Trackingnr & "+&track.x=Track"""

Range("A" & activerow).Formula = "=HYPERLINK(" & mytrace & ",""" & Trackingnr & """)" 'replace "A" with the actual column
activerow = activerow + 1
Loop

End Sub
[/VBA]

Job done!

[Emoncada]

Seems to work is there anyway I can not have it hyperlink the first two rows? Which are column Headers.

[Emoncada]

forget it saw your comment.

[Emoncada]

Excellent ukdane!!!

Thanks Again.

[JimS]

x