Adding Hyperlinks based on cell contents

[KelvinLGW]

Hi,

Below is an example of my excel table:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]\Users\klee3\Desktop\18DP ESO\3_Off Tool Wins[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.pdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2.pdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3.pdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4.pdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5.pdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6.pdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So in Cell C2 is a UNC path which I have a macro to retrieve (folder only).
In Cells G5 all the way to last row used is the document names.

What I need a code to do is to go through each cell from G5 to last cell that has a value in it (i.e. last row used) and create a hyperlink using C2 & "" & (G5 all the way to the last cell in range G used).

I'm sure it's an easy code, but for some reason I cannot figure it out.

Any help would be much appreciated.

Thanks,

Kelvin

 

[Roderick_E]

=hyperlink($c$2 & g5,"LINK")

However it looks like your path is not correct since it doesn't end with a blackslash.

 

[KelvinLGW]

Sorry I should have added that I wanted this done on VBA.

 

[Perpa]

Sorry I should have added that I wanted this done on VBA.

KelvinLGW,
Give this a try:
Perpa

Sub hyperLINKcreate()
    Dim nextRow As Integer, x As Integer
    Dim myLink As Hyperlink
    Dim strSubAddress As String
    Dim xOriginal As String
    Dim LastRow As Integer
    
    LastRow = Cells(Rows.Count, "G").End(xlUp).Row

    For x = 5 To LastRow
        Cells(x, 7).Activate
        xG = ActiveCell.Text
        xOriginal = ActiveCell.Value
    
        If xOriginal = "" Then GoTo mycell
        If IsNumeric(xG) Then GoTo mycell
            With ActiveSheet
                Set myLink = .Hyperlinks.Add(ActiveCell, .Cells(2, "C").Value & Application.PathSeparator & xG, xG)
            End With
            
        'End If not required where the "IF' is all inclusive as above
mycell:
    Next x

End Sub

 

[Perpa]

KelvinLGW,
Give this a try:
Perpa

Sub hyperLINKcreate()
    Dim nextRow As Integer, x As Integer
    Dim myLink As Hyperlink
    Dim strSubAddress As String
    Dim xOriginal As String
    Dim LastRow As Integer
    
    LastRow = Cells(Rows.Count, "G").End(xlUp).Row

    For x = 5 To LastRow
        Cells(x, 7).Activate
        xG = ActiveCell.Text
        xOriginal = ActiveCell.Value
    
        If xOriginal = "" Then GoTo mycell
        If IsNumeric(xG) Then GoTo mycell
            With ActiveSheet
                Set myLink = .Hyperlinks.Add(ActiveCell, .Cells(2, "C").Value & Application.PathSeparator & xG, xG)
            End With
            
        'End If not required where the "IF' is all inclusive as above
mycell:
    Next x

End Sub

KelvinLGW,
One slight change to the last 'WITH' statement:

[TABLE="width: 48"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]With ActiveSheet[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Set myLink =  .Hyperlinks.Add(ActiveCell, .Cells(2, "C").Value &  Application.PathSeparator & xG)

End With[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[KelvinLGW]

That worked perfectly perpa, exactly what I wanted. Thanks so much.

 

[Perpa]

That worked perfectly perpa, exactly what I wanted. Thanks so much.

You are welcome...glad that worked for you.
Perpa