Put an hyperlink using a macro in an excel report

When I am working with a report that contains for instance problem ticket number and the related configuration item, I may need to open the ticket to understand better the situation and to get more details about the device in a CMDB intranet webpage. Both can be opened via an hyperlink so this code creates quickly those hyperlinks.

formula excel formula excel


When I use the macro ?

For instance, if a CI can be opened via an hyperlink.


How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report


How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report


How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
Dim i As Integer
i = 2
With ActiveSheet
' if problem ticket in the column B=2 until the row 50, put an hyperlink, if no, nothing
Do While (Cells(i, 2).Value <> "") And (i < 50)
' change column number 2=B where to have hyperlink
' change xxx by your intranet webpage
ActiveSheet.Hyperlinks.Add Cells(i, 2), "https://xxx" & Trim(Cells(i, 2).Value)
i = i + 1
End With
End Sub

Interesting Management