Adding Hyperlinks based on cell contents

[KelvinLGW]

Hi,

Below is an example of my excel table:

	A	B	C	D	E	F	G	H	I
1
2			\Users\klee3\Desktop\18DP ESO\3_Off Tool Wins
4
5							1.pdf
6							2.pdf
7							3.pdf
8							4.pdf
9							5.pdf
10							6.pdf

<tbody>
</tbody>

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