copying the Hyperlink function result without copying the actual formula

[mcheng]

Hi, What I'm hoping to do is to copy the =Hyperlink(A1,B1) result onto
a new cell with the actual linked text, but without the formula. So
if A1 has "http://google.com" and B1 has "link", then rather than
using the above formula (which will read the same on the cell), the
destination cell will read "link" hyperlinked to http://google.com
without the =hyperlink formula. The reason I want to do this copying
is to reuse the cells A1 and B1 for other entries. Can anyone help?
Thanks.
~michael

 

[Guest]

does
="""" & B1 & """" & " hyperlinked to " & A1
do what you want?

 

[mcheng]

Thanks bj, but no because i want to somehow capture the contents of b1
without it being there after the procedure. (i.e. cut and paste
rather than reference). So I'm wondering if is there a way to write a
UDF so that I'm using paste.special with values and hyperlink retained
and without the formula....

Or, if there's a UDF to direct the content of the quotations below to
the contents of the cell?
(i.e.
ActiveCell.FormulaR1C1 = "link"
Range("B2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http://
google.com" , TextToDisplay:="link")

rather than "link" specified, to have it refer it to B1 and the
Address:= be specified to the content of A1?

Thanks again,
Michael