Update hyperlink value with cell value

[Guest]

Excel 2002 SP3

I would like to know how to achieve the following with a vba script or macro:

verify if the value of a hyperlink is the same a the value of the cell, e.g.
if the value of a cell is (e-mail address removed) and the email hyperlink value is
(e-mail address removed)
then -> ok (leave as it is)

but

if the value of a cell is (e-mail address removed) and the email hyperlink value is
(e-mail address removed)
then -> not ok (update email hyperlink with value of the cell)

and
when the cell is empty and hyperlink is filled in, cell should be updated.



Can this be scripted?
Thanks for any help as I don't seem to manage it right now.


Regards,
Bert

 

[Guest]

This assumes that the displayed text does NOT include mailto:
If the displayed text is something like:

(e-mail address removed)

then:

Sub hyperfix()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Address = "mailto:" & h.TextToDisplay
Next
End Sub

 

[Guest]

thanks a lot for the solution. It does what it should except on thing.

Can you build in this that when the cell value is empty, nothing should
happen?

 

[Guest]

Sub hyperfix()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
If h.TextToDisplay = "" Then
Else
h.Address = "mailto:" & h.TextToDisplay
End If
Next
End Sub

This version will ignore cells that do not display anything.

 

[Guest]

Gary,


The first sub did really what had to be done, but with the new version,
after selecting the column where it need to be applied to and running this
saved macro, nothing happend.
Cells with a wrong hyperlink still had this after running the script.

Do I need to add something in the line
If h.TextToDisplay = "" Then

Because I'm not sure that if either the displayed cell or the hyperlink cell
is not empty, this doesn't apply to it...

Does this script take into account that we are talking about two different
values?