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

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
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:
Code:
[I]
=Hyperlink( ".\" & "PDFS" & "\" Substitute(A1," ", "") & ".pdf", A1)
[/I]

Anyone?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi Jeff,

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

Code:
Sub AddFormulas()

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

End Sub
 
Upvote 0
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.

Code:
Sub AddFormulas2()

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

End Sub
 
Upvote 0
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.

Code:
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?
 
Upvote 0
This code will add hyperlink objects to each non-blank selected cell.

Code:
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
 
Upvote 0
This code will add hyperlink objects to each non-blank selected cell.

Code:
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!
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top