CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

Making a dynamic Hyperlink function static

I had a column of values in column A, all with 6 digit numbers. In order to hyperlink each of these to a folder containing PDF files with the same 6-digit titles, I inserted the following function into cell A2:

=HYPERLINK("C:\...Project\" & A2 & ".pdf", A2)

This worked perfectly, and dragging it down through the thousand or so cells created hyperlinks for each entry.

However, now I want to replace column A1 with these new hyperlinks. I thought I could paste-special the column of hyperlinks to remove the dynamic entries in each cells, but this doesn't seem to be possible. I'm sure there's an easier solution. Am I missing something? Can I make column B exist independent of column A before replacing it? Is there a VBA solution?

Upvotes: 3

Views: 978

Answers (2)

Slai
Slai

Reputation: 22876

If by any chance anyone wants to do it without VBA, you can replace for example

=HYPERLINK(1,2)

with

<html><a href=1>2

but in cases with dynamic formulas it's too complicated because 

=HYPERLINK("C:\...Project\" & A2 & ".pdf", A2)

will be

="<html><a href=""C:\...Project\" & A2 & ".pdf"">" & A2

and then it has to be copied as text (for example paste the values in Notepad and copy them again in Notepad) before pasting in Excel.

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

You can run this quickly in vba procedure:

Sub CreateHyperlinks()
Dim cl As Range

For Each cl In Range("B1:B100").Cells '## Modify as needed
    cl.Hyperlinks.Add cl, cl.Value, , , cl.Offset(0,-1).Value
Next

End Sub

Upvotes: 3

Related Questions