reading the hyperlink

[Neil Jarman]

Hi,

I have a column of hyperlinked cells, and I need to read the value of the
hyperlink in order to process it.

eg: say I needed to get the last 10 chars from the hyperlink in Ax and put
it in Cx,
I want to write some code like this

Private Sub CommandButton1_Click()

Dim iMaxRow
iMaxRow = 5 ' 648

For iIndex = 1 To iMaxRow
Worksheets("Sheet1").Cells(iIndex, 3).Value =
Right$(Worksheets("Sheet1").Cells(iIndex, 1).Hyperlink.Address, 10)
Next iIndex

End Sub

Obviously this doesn't work, or I wouldn't be asking...

Any advice really appreciated. Found this group after searchng MS.com.

Cheers,

NEIL

 

[Norman Jones]

Hi Neil,

One way:

Private Sub CommandButton1_Click()
Dim iIndex As Long
Dim iMaxRow As Long
iMaxRow = 5 ' 648

For iIndex = 1 To iMaxRow
Worksheets("Sheet1").Cells(iIndex, 3).Value = _
Right$(Worksheets("Sheet1").Cells(iIndex, 1). _
Hyperlinks(1).Address, 10)
Next iIndex

End Sub