Hyperlink Madness

[BaldySlaphead]

I have two columns in my worksheet. Column A is supposed to be a list of
business names, without hyperlinks. Column B is supposed to be a list
of hyperlinks to their websites, giving the URL.

I currently have a list of company names in Column A, which are
hyperlinks to the websites.

________________________________
| |
| COLUMN A | COLUMN B
|_______________|________________
| |
| Excel Forum | www.excelforum.com
|_______________|________________
| |
| Fresh Minds | www.freshminds.co.uk
|_______________|________________
| |
| etc | etc
|_______________|________________
| |

It is imperative that I can see the whole URL in column B, regardless
of how stupid this may appear to be - I do realise this appears to be
duplication of effort.

My question is: How can I copy just the URLs from all of Column A, and
post 'em in all of Column B? Can I do this by exporting or something?

Any solutions, gratefully received,

Cheers,

Baldy

 

[BaldySlaphead]

That formatting was *a lot* better before I posted it!

 

[Dan E]

Baldy,

Here's a user defined function to do what your requesting

Public Function HyperLinkAddress(cell As Range)
HyperLinkAddress = cell.Hyperlinks.Item(1).Address
End Function

Instructions
Press Alt + F11 (opens VBA)
In the project explorer (left hand side) find your project
(ie VBAProject(yourbookname)) and expand it...
right click (your project) and choose "insert" -> "module"
A code window should appear (if it doesn't double click
module1).
Paste the above code into your code window
Close the VBA window

In your function wizard you should now find a function
called HyperLinkAddress

In an empty column put
=HyperLinkAddress(A1) and drag down
Copy and paste special as values where you would
like to see the addresses.

You can now delete the code module
Alt + F11
Expand your project
Right click on module1 and choose "Remove Module1"
You don't need to export it.

Dan E

 

[Anders S]

One way is to use a user defined function, UDF.

Paste the following code in a module in VBA - do Alt+F11 and Insert->Module:

'--------
Option Explicit

Function getURL(cellRef As Range) As String
getURL = cellRef.Hyperlinks(1).Address
End Function
'--------

Then, in B2 enter
=getURL(A2)

HTH
Anders Silvén

 

[Anders S]

Use a monospaced (fixed width) font, such as Courier.

Anders

 

[BaldySlaphead]

One way is to use a user defined function, UDF.

Paste the following code in a module in VBA - do Alt+F11 and
Insert->Module:

'--------
Option Explicit

Function getURL(cellRef As Range) As String
getURL = cellRef.Hyperlinks(1).Address
End Function
'--------

Then, in B2 enter
=getURL(A2)

HTH
Anders Silvén

---------------

Anders and Dan,

Thank you both, very much - extremely useful indeed!

Regards,

Baldy