How Do I Trap #VALUE!

  • Thread starter Thread starter Robert P. Stearns
  • Start date Start date
R

Robert P. Stearns

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!?
 
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
 
Back
Top