0 votes
in VBA by Beginner (5 points)

I used a "Macro to extract the URL" formula in my excel sheet and I am getting a #VALUE error. Is there something that I missed? I came accross your website because I have been researching ways to extract the url's from hyperlinks I got from a website. However, the VBA macros I have been trying to use do not work fully. I only get part of the url and it does not get anything past the pound sign. As mentioned when I tried using your VBA, I am getting a #VALUE error. Any help would be greatly appreciated because I have over 1000 hyperlinks.

by Beginner (5 points)

Full url: https://www.acquisition.gov/content/part-3-improper-business-practices-and-personal-conflicts-interest#i1124578
Link from other VBA: https://www.acquisition.gov/content/part-3-improper-business-practices-and-personal-conflicts-interest    

Other VBA code:  

Function Hlink(rng As Range)
'Extracts the url from the hyperlink
    On Error Resume Next
    Hlink = rng.Hyperlinks(1).Address
    If Hlink = 0 Then Hlink = ""
End Function


1 Answer

+1 vote
by Super Expert (1.9k points)
selected by
Best answer

To grab the URL anchor (anything after the # pound sign) at the end of the hyperlink, you'll need to call the SubAddress property of the hyperlink. Try modifying your macro to look like this:

Function Hlink(rng As Range)
'Extracts the url from the hyperlink'
    On Error Resume Next
    If rng.Hyperlinks(1).SubAddress <> "" Then
        Hlink = rng.Hyperlinks(1).Address & "#" & rng.Hyperlinks(1).SubAddress
        Hlink = rng.Hyperlinks(1).Address
    End If
    If Hlink = 0 Then Hlink = ""
End Function


by Beginner (5 points)
THANK YOU SOOO MUCH!!!!!!!!!! This is a life SAVER!!!!

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and/or answers. Users that ask great questions, may be given complimentary gift cards or training material.

Getting Started

So, why don't you join us? It really is a neat way to reward the most dedicated members in our VBA and Python community.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.