How Do I Trap #VALUE!
 

I would like to extract the hyperlink from a cell.
I found a nice example on http://www.ozgrid.com/VBA/HyperlinkAddress.htm
which defined the function GetAddress as:

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function
In cell A2, you can enter the function =GetAddress(A1) to extract the
hyperlink from cell A1.In other cells where I do not have a hyperlink the
function returns #VALUE!. How do I change the function to return "" instead
of #VALUE!?

How Do I Trap #VALUE!
 

Robert,

Function GetAddress(HyperlinkCell As Range)
GetAddress = ""
On Error Resume Next
GetAddress = Replace( _
HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

HTH,
Bernie
MS Excel MVP

"Robert P. Stearns" wrote in message
news:zROYb.71578$uV3.527746@attbi_s51...
I would like to extract the hyperlink from a cell.
I found a nice example on http://www.ozgrid.com/VBA/HyperlinkAddress.htm
which defined the function GetAddress as:

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function
In cell A2, you can enter the function =GetAddress(A1) to extract the
hyperlink from cell A1.In other cells where I do not have a hyperlink the
function returns #VALUE!. How do I change the function to return ""
instead
of #VALUE!?

How Do I Trap #VALUE!
 

Bernie,

That did exactly what I wanted.

Thank You!

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Robert,

Function GetAddress(HyperlinkCell As Range)
GetAddress = ""
On Error Resume Next
GetAddress = Replace( _
HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

HTH,
Bernie
MS Excel MVP

"Robert P. Stearns" wrote in message
news:zROYb.71578$uV3.527746@attbi_s51...
I would like to extract the hyperlink from a cell.
I found a nice example on http://www.ozgrid.com/VBA/HyperlinkAddress.htm
which defined the function GetAddress as:

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace HyperlinkCell.Hyperlinks(1).Address, "mailto:",
"")
End Function
In cell A2, you can enter the function =GetAddress(A1) to extract the
hyperlink from cell A1.In other cells where I do not have a hyperlink
the
function returns #VALUE!. How do I change the function to return ""
instead
of #VALUE!?