Adding characters to hyperlinks.

[Sir Babydum GBE]

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.

[Sir Babydum GBE]

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!

[Killian]

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]

[Sir Babydum GBE]

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.

[Killian]

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

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.

[Sir Babydum GBE]

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.