VBA code to rename hyperlink according to value in each row

[leonc337]

Greetings all,

I'm relatively new to VBA and I would like some help on a problem I'm trying to solve for. I have two columns of data and both of them are text initially.
I first ran the VBA below to turn texts in the "Hyperlink" column into clickable links. However, I would also like them to be renamed to the corresponding friendly name using texts showed on the left column. Is there a VBA code that can make this work? If the 2 steps (turn text into hyperlink + rename) can be combined into one that's better. <*** I would only want VBA solution because some of the link exceed formula text limit so formula won't work>

Many Thanks!!!

 

[Alex Blakenburg]

Give this a try:-

Sub CreateHyperlink()
    Dim rCell As Range

    For Each rCell In Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        ActiveSheet.Hyperlinks.Add Anchor:=rCell, Address:=rCell.Value, TextToDisplay:=rCell.Offset(, -1).Value
    Next rCell
End Sub

 

[JohanGduToit]

This does the trick for converting only cells in column I that contains a value:

Dim rCell as Range
For Each rCell In .Application.Range("I2:I" & .Application.Cells(Rows.Count, "I").End(xlUp).Row)
            If IsEmpty(rCell.Value) = False Then
                .Application.ActiveSheet.Hyperlinks.Add Anchor:=rCell, Address:=rCell.Value, TextToDisplay:=rCell.Offset(, -3).Value
            End If
Next rCell