Excel VBA Get hyperlink address of specific cell

Question

How do I code Excel VBA to retrieve the url/address of a hyperlink in a specific cell?

I am working on sheet2 of my workbook and it contains about 300 rows. Each rows have a unique hyperlink at column "AD". What I'm trying to go for is to loop on each blank cells in column "J" and change it's value from blank to the hyperlink URL of it's column "AD" cell. I am currently using this code:

do while....
    NextToFill = Sheet2.Range("J1").End(xlDown).Offset(1).Address
    On Error Resume Next
    GetAddress = Sheet2.Range("AD" & Sheet2.Range(NextToFill).Row).Hyperlinks(1).Address
    On Error GoTo 0
loop

Problem with the above code is it always get the address of the first hyperlink because the code is .Hyperlinks(1).Address. Is there anyway to get the hyperlink address by range address like maybe sheet1.range("AD32").Hyperlinks.Address?

Range("A1").Hyperlinks(1).Address will get you the first hyperlink in cell A1. sheet1.range("AD32").Hyperlinks(1).Address will give you the first hyperlink in cell AD32 — Jzz
– Jzz, Commented Jun 1, 2014 at 21:35
Thanks Jzz, but if you look at my code above, it should be changing rows and so GetAddress should be changing but it always return the hyperlink address of the first link it detected. I forgot to mention this code is wrapped in a do while loop. I will edit the post. — user3682866
– user3682866, Commented Jun 1, 2014 at 21:53
So basically your asking how to loop rows? Try google for that. If it is anything else, clearify your question. — Jzz
– Jzz, Commented Jun 1, 2014 at 21:57
No, I'll try again. Cells in column "J" contains the text version of the URL/Address of hyperlinks in Cells of column "AD". Like cell "J23" contains the URL/address of the hyperlink in cell "AD23". Now, there are blank cells in column "J", ones that do not have the text version of hyperlinks in column "AD" yet. I am trying to loop through each of those blank cells in column "J", look to it's "AD" column, extract the url/address of that hyperlink and place it to the cell in it's "J" column. That's my code above. I was wondering if there's such a code like sheet1.range("AD23").Hyperlinks.Address — user3682866
– user3682866, Commented Jun 2, 2014 at 5:21
My code is basically doing a Sheet2.Range("AD23").Hyperlinks(1).Address and then a Sheet2.Range("AD24").Hyperlinks(1).Address and a Sheet2.Range("AD25").Hyperlinks(1).Address but they all output the hyperlink address of "AD23" — user3682866
– user3682866, Commented Jun 2, 2014 at 5:24

FreeSoftwareServers · Accepted Answer · 2021-02-15 16:22:37Z

6

This IMO should be a function to return a string like so.

Public Sub TestHyperLink()
 Dim CellRng As Range
 Set CellRng = Range("B3")
 
 Dim HyperLinkURLStr As String
 HyperLinkURLStr = HyperLinkURLFromCell(CellRng)
 Debug.Print HyperLinkURLStr
End Sub

Public Function HyperLinkURLFromCell(CellRng As Range) As String
 HyperLinkURLFromCell = CStr(CellRng.Hyperlinks(1).Address)
End Function

answered Feb 15, 2021 at 16:22

FreeSoftwareServers

2,8886 gold badges43 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JoeG · Accepted Answer · 2019-07-12 17:28:40Z

5

Not sure why we make a big deal, the code is very simple

Sub ExtractURL()
    Dim GetURL As String
    For i = 3 To 500
        If IsEmpty(Cells(i, 1)) = False Then
            Sheets("Sheet2").Range("D" & i).Value = 
               Sheets("Sheet2").Range("A" & i).Hyperlinks(1).Address
        End If
    Next i
End Sub

answered Jul 12, 2019 at 17:28

JoeG

1922 silver badges3 bronze badges

1 Comment

Chris Peh Over a year ago

Where is the GetURL variable which is defined used?

Jason K. · Accepted Answer · 2015-03-19 18:19:59Z

3

This should work:

Dim r As Long, h As Hyperlink
For r = 1 To Range("AD1").End(xlDown).Row
    For Each h In ActiveSheet.Hyperlinks
        If Cells(r, "AD").Address = h.Range.Address Then
            Cells(r, "J") = h.Address
        End If
    Next h
Next r

It's a bit confusing because Range.Address is totally different than Hyperlink.Address (which is your URL), declaring your types will help a lot. This is another case where putting "Option Explicit" at the top of modules would help.

answered Mar 19, 2015 at 18:19

Jason K.

4174 silver badges12 bronze badges

Comments

D Mason · Accepted Answer · 2014-06-03 13:29:40Z

My understanding from the comments is that you already have set the column J to a string of the URL. If so this simple script should do the job (It will hyperlink the cell to the address specified inside the cell, You can change the cell text if you wish by changing the textToDisplay option). If i misunderstood this and the string is in column AD simply work out the column number for AD and replace the following line:

fileLink = Cells(i, the number of column AD)

The script:

Sub AddHyperlink()

Dim fileLink As String

Application.ScreenUpdating = False

With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row


For i = 4 To lastrow

    fileLink = Cells(i, 10)

    .Hyperlinks.Add Anchor:=Cells(i, 10), _
    Address:=fileLink, _
    TextToDisplay:=fileLink

Next i

End With

Application.ScreenUpdating = True

End Sub

Lorenz Meyer · Accepted Answer · 2015-01-31 07:09:18Z

0

Try to run for each loop as below:

do while....
    NextToFill = Sheet2.Range("J1").End(xlDown).Offset(1).Address
    On Error Resume Next
    **for each** lnk in Sheet2.Range("AD" & Sheet2.Range(NextToFill).Row).Hyperlinks
         GetAddress=lnk.Address
    next
On Error GoTo 0
loop

edited Jan 31, 2015 at 7:09

Lorenz Meyer

20.1k23 gold badges83 silver badges130 bronze badges

answered Jan 31, 2015 at 6:51

abhinov

1541 gold badge3 silver badges14 bronze badges

2 Comments

Jason K. Over a year ago

In this case lnk is a cell of type range, not a hyperlink.

abhinov Over a year ago

No its a hyperlink if you observe closely..Sheet2.Range("AD" & Sheet2.Range(NextToFill).Row).Hyperlinks, a collection of hyperlinks from a given Range

Collectives™ on Stack Overflow

Excel VBA Get hyperlink address of specific cell

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related