Still struggling... how make a hyperlink not hot!

[Darin Kramer]

Howdie,
I have a formulae with the hyperlink in column A which states
HYPERLINK("mailto:"&K13,J13)

I want to convert that to "text" - ie not a formulae, but still a
hyperlink that uses the contents of the hyperlink to populate the cell
(ie instead of k13 put what is in k13 and instead of j13 what is in j13)
I need to run it over the whole of column A

Use VB recording I see the following when a hyperlink is created:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:[email protected]", TextToDisplay:="John"

I somehow need to integrate this above VB with my existing data...
HELP!!

Regards

D

 

[james.billy]

Howdie,
I have a formulae with the hyperlink in column A which states
HYPERLINK("mailto:"&K13,J13)

I want to convert that to "text" - ie not a formulae, but still a
hyperlink that uses the contents of the hyperlink to populate the cell
(ie instead of k13 put what is in k13 and instead of j13 what is in j13)
I need to run it over the whole of column A

Use VB recording I see the following when a hyperlink is created:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:[email protected]", TextToDisplay:="John"

I somehow need to integrate this above VB with my existing data...
HELP!!

Regards

D

*** Sent via Developersdexhttp://www.developersdex.com***

Unless I have misinterpreted what you are asking for you want your
code to create a hyperlink based on cell values:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:[email protected]", TextToDisplay:=Range("J13") &
Range("K13")

James

 

[Darin Kramer]

Yes but I dont want to hard code the mailto:john bit ie instead of john
I need a cell reference...

 

[JE McGimpsey]

*Real* quick and dirty:

Public Sub ConvertHyperlinks()
Dim rCellsToCheck As Range
Dim rCell As Range
Dim nPos As Long
Dim sParse As String
Dim sTextCellRef As String
Dim sAddrCellRef As String
On Error Resume Next
Set rCellsToCheck = _
ActiveSheet.Columns(1).Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rCellsToCheck Is Nothing Then
For Each rCell In rCellsToCheck
With rCell
nPos = InStr(.Formula, "mailto:""&")
If nPos > 0 Then
sParse = Mid(.Formula, nPos + 9)
nPos = InStr(sParse, ",")
sAddrCellRef = Left(sParse, nPos - 1)
sParse = Mid(sParse, nPos + 1)
nPos = InStr(sParse, ")")
sTextCellRef = Left(sParse, nPos - 1)
.ClearContents
.Parent.Hyperlinks.Add _
Anchor:=.Cells, _
Address:=.Parent.Range(sAddrCellRef).Text, _
TextToDisplay:=.Parent.Range(sTextCellRef).Text
End If
End With
Next rCell
End If
End Sub

 

[Darin Kramer]

Okay I think I got it...
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:" & Range("b2"), TextToDisplay:=Range("a2") &
Range("k2")

But.... now I want to make this loop from rows 1 to say 150, taking
a2,b2,k2 and then a3,b3,k3 etc - with the answer placed in column L2 and
then L3 etc

REALLY appreciate the help...

Regards

D

 

[james.billy]

Unless I have misinterpreted what you are asking for you want your
code to create a hyperlink based on cell values:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:[email protected]", TextToDisplay:=Range("J13") &
Range("K13")

James- Hide quoted text -

- Show quoted text -

In my previous post I had replaced John with a cell reference. So if
your email address is in K13 and name in J13 then try this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= Range("K13"),
TextToDisplay:=Range("J13")

James

 

[james.billy]

Okay I think I got it...
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:" & Range("b2"), TextToDisplay:=Range("a2") &
Range("k2")

But.... now I want to make this loop from rows 1 to say 150, taking
a2,b2,k2 and then a3,b3,k3 etc - with the answer placed in column L2 and
then L3 etc

REALLY appreciate the help...

Regards

D

*** Sent via Developersdexhttp://www.developersdex.com***

Hi,

To loop, use something like this:


Dim xRng as Range, xCell as Range
Set xRng = Range("L1:L150") 'Change to represent your output range.
For each xCell in xRng
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:= _
"mailto:" & xcell.offset(0,-10),
TextToDisplay:=xcell.offset(0,-11) & xcell.offset(0,-1)
Next xCell

Get any problems then let me know,

James

 

[Guest]

If your hyperlink formulas are in L10:L20, then this macro does what you
want. Change L10:L20 to indicate the cells with the hyperlink formula.

Sub ABC1()
Dim cell As Range, r As Range
Dim s As String, s1 As String
For Each cell In Range("L10:L20")
If InStr(1, cell.Formula, "hyperlink", vbTextCompare) Then
Set r = cell.DirectPrecedents(1)
s = "mailto:" & Range("K" & r.Row)
s1 = Range("J" & r.Row)
'Debug.Print s, s1
cell.ClearContents
ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:=s, TextToDisplay:=s1
End If
Next

Test it on a copy of your worksheet.

 

[Guest]

This should do what you want:

Sub HyperLink()
Dim hlnk As HyperLink
For Each cell In Range("A1:A50") 'Set range to suit
For Each hlnk In cell.Hyperlinks
cell.Offset(0, 1).Value = hlnk.Address
Next
Next

End Sub

 

[JE McGimpsey]

Did you actually try it?

Using the XL HYPERLINK() function doesn't create a hyperlink object, so
the Hyperlinks collection will be empty.

 

[Darin Kramer]

Thanks Tom (and others!!) works like a charm now!!! This group is
amazing!!

Regards

D