Macro: Can you Help Me Convert this formula to Macro? Auto-hyperlink selected cells

[jeffcoleky]

Two great board members helped me create this formula to auto-hyperlink my cells based on their contents HERE. Since it's a new question i'm starting a new post.

I need my auto-hyperlinking formula to be converted to a macro instead. Can anyone help?

The macro needs to be applied to the SELECTED cells when run.

Here's the Formula:

[I]
=Hyperlink( ".\" & "PDFS" & "\" Substitute(A1," ", "") & ".pdf", A1)
[/I]

Anyone?

 

[Jerry Sullivan]

Hi Jeff,

Assuming you want a macro to insert the formula into column B, you could try this...

Sub AddFormulas()

With Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
    .FormulaR1C1 = "=HYPERLINK("".\PDFS\""& SUBSTITUTE(RC1,"" "", """") & "".pdf"", RC1)"
End With

End Sub

 

[Jerry Sullivan]

The code I posted will add formulas to column B corresponding to the data range in column A.

I just noticed that you asked for a macro to apply to Selected Cells.

Assuming you will select cells in column B or another column other than column A, you could use this variation.

Sub AddFormulas2()

With Selection
    .FormulaR1C1 = "=HYPERLINK("".\PDFS\""& SUBSTITUTE(RC1,"" "", """") & "".pdf"", RC1)"
End With

End Sub

 

[jeffcoleky]

The code I posted will add formulas to column B corresponding to the data range in column A.

I just noticed that you asked for a macro to apply to Selected Cells.

Assuming you will select cells in column B or another column other than column A, you could use this variation.

Sub AddFormulas2()

With Selection
    .FormulaR1C1 = "=HYPERLINK("".\PDFS\""& SUBSTITUTE(RC1,"" "", """") & "".pdf"", RC1)"
End With

End Sub

To clarify: I need the macro to modify the selected cells by converting them to hyperlinks. The text in the cell remains the same, but the hyperlink is added.

In my original example I would end up with the same values for each cell in column A but they would each be hyperlinked to ".\pdfs\[cellvalue].pdf" (excluding the spaces)

Does that make sense?

 

[Jerry Sullivan]

This code will add hyperlink objects to each non-blank selected cell.

Sub Add_Hyperlinks()
    Dim c As Range
    Dim sAddress As String
    
    For Each c In Selection
        If c <> "" Then
            sAddress = "PDFS\" & _
                Replace(c.Text, " ", "") & ".pdf"
            c.Parent.Hyperlinks.Add _
                Anchor:=c, Address:=sAddress, _
                TextToDisplay:=c.Text
        End If
    Next c
    
End Sub

 

[jeffcoleky]

This code will add hyperlink objects to each non-blank selected cell.

Sub Add_Hyperlinks()
    Dim c As Range
    Dim sAddress As String
    
    For Each c In Selection
        If c <> "" Then
            sAddress = "PDFS\" & _
                Replace(c.Text, " ", "") & ".pdf"
            c.Parent.Hyperlinks.Add _
                Anchor:=c, Address:=sAddress, _
                TextToDisplay:=c.Text
        End If
    Next c
    
End Sub

PRECISELY What i needed. Thank you!

 

[mielkew]

PRECISELY What i needed. Thank you!

Hi Jerry,

Can we modify this VBA Code?

Let say I have my excel file in different folder location, (i.e. D: drive) and the files to be hyperlink are in Local server (F: Drive = shared network "Mapped").

Also, if name in excel is NO MATCH found in the folder should NOT hyperlink.


Sub Add_Hyperlinks()
Dim c As Range
Dim sAddress As String

For Each c In Selection
If c <> "" Then
sAddress = "PDFS" & _
Replace(c.Text, " ", "") & ".pdf"
c.Parent.Hyperlinks.Add _
Anchor:=c, Address:=sAddress, _
TextToDisplay:=c.Text
End If
Next c

End Sub

 

[jeffcoleky]

You should make a new thread and reference this one in the post.

 

[mielkew]

You should make a new thread and reference this one in the post.

Will do jeffcoleky! Thanks