Adding characters to hyperlinks.

Good morning!

I have taken some time out of my busy schedule of organising world peace to work on a spreadsheet on which the hyperlinks do not work.

When a web table was pasted onto Excel, the links were pasted too, but they were missing the first part of the address. e.g.

http://www.vbaexpress.com/forum/newt...newthread&f=17
would be pasted as:
forum/newthread.php?do=newthread&f=17

So how can I write a macro that would put (in this example) "http://www.vbaexpress.com/" in front of all the current links in my selection to make them complete again?

Some cells are empty, and not all cells are hyperlinks, by the way.

Thanks a million.

Ok, I got part of the answer.

I fount code that turns the hyperlinkf riendly name to the actual hyperlink text, then used HYPERLINK() in a different column and concatenated the missing text.

Problem now is my sheet is cumbersome. I thought Id be able to change the formulas to values and paste them over the broken links - except that paste values removes the link altogether.

So... can I change this:
[VBA]=IF(ISBLANK(H4),0,HYPERLINK("<A href="http://m2k4901a/R3_Roll_Out/"&H4,N4">http://m2k4901a/R3_Roll_Out/"&H4,N4))[/VBA]
to this friendly name (which is returned because it appears in cell N4 of the above formula):
[VBA]3ABI01 Course[/VBA]
Without losing the link that the formula creates?
My head is just about done in.

Of course, if you found an answer to the first question, then that's great!

Keen to get you back to addressing issues of world peace, can I suggest a visit to the Hyperlinks collection?
Something like[VBA]Dim h As Hyperlink

For Each h In Worksheets(1).Hyperlinks
If Left(h.Address, 4) <> "http" Then
h.Address = "http://www.vbaexpress.com/" & h.Address
End If
Next[/VBA]

What the blazes! You're macro did in 0.11649781268 seconds what I'd spent all of today so far trying to do.

Marry me.

Quote:

---

Originally Posted by Babydum

What the blazes! You're macro did in 0.11649781268 seconds what I'd spent all of today so far trying to do.

---

Well good... that's what they're for :yes

Quote:

---

Originally Posted by Babydum

Marry me.

---

Well I hardly think that generating efficiency through judicious use of code is a suitable foundation from which to launch a life-long partnership.


Unless you're rich... then maybe.


Or you're really Winona Ryder... then yes.

Quote:

---

Originally Posted by Killian

Well I hardly think that generating efficiency through judicious use of code is a suitable foundation from which to launch a life-long partnership.

---

I must be reading the wrong books! Harrops book Suitable Foundations From Which to Launch Life-long Partnerships says, spookily enough, (on page 33)"The most suitable foundation from which to launch a life-long partnership is the generation of efficiency through judicious use of code".

But don't worry, I was only kidding - I have a feeling you're the wrong sex for me.

Plus, I have world peace to sort out.