How to extract a hyperlink from an Excel cell

Question

I've searched on this and found multiple like posts, but cannot get anything to work for me.  I am a total novice when it comes to VB, but can usually muddle through when given instructions.

I'm running Mac Office 2011.

I have a worksheet (named "multiples") in the following format:

Reference	State	Multiple	Text
Number		Name	Click Me
64000005	ALABAMA	19th Century Spring Hill Neighborhood TR	Text
64000006	ALABAMA	Anniston MRA	Text

The TEXT field has a URL. I would like to extract the hyperlink from it and put it into the adjacent cell.

I found this macro:

Sub hyper()

Dim sht As Worksheet: Set sht = Worksheets("multiples")

Dim cll As Range

    For Each cll In sht.Range("e8:e15")

        sht.Cells(cll.Row, cll.Column + 1).Value = cll.Hyperlinks(1).Address

    Next cll

End Sub

When I try to run it tho, it stops at the 5th line and I get a "Run-time error '9': Subscript out of range" error and it highlights the entire line.

Could someone please help me with this.  I'm just looking for the easiest way to achieve this goal and I cannot figure it out on my own.

Many thanks for any guidance.

Answer 1

Your "Run-time error '9' happens because cll.Hyperlinks(1).Address is not a valid property, most likely a cell that doesn't have any hyperlinks.

To fix this, add an "if" statement:

        If cll.Hyperlinks.Count > 0 Then

            sht.Cells(cll.Row, cll.Column + 1).Value = cll.Hyperlinks(1).Address

        End If

Worked well for my purposes. I had 72 rows from which 50 had hyperlinks and the others were just text or blank.

Answer 2

Jim:

Thank you very much for your efforts - I appreciate it.

Yes, I really did have the range of cells selected before I ran the macro - I really did.  I just tried it again with the same result.

The really funny thing (embarrassing, really) is that the URLs were right there all along, just in HIDDEN cells!  I just didn't tumble to that until I got to looking very closely what what I had posted and realized that the cells went A, B, C, E, F and that D was missing, or rather, hidden.

So I can access the links without any macro.

Once again, thank you very much.  Next time I will try to be more observant before posting an SOS.

Answer 3

I just tried using links to the adjacent column just as you showed in your example. I tried with the link target visible as well as hidden and the macro listed the link.

Are you sure you selected the range of cells before you ran the macro?