Hyperlink trouble - 2000

[Dan_WI]

I apologize if this has been asked before, but I didn't find it in a search

Details
Excel 2000
List of 400 hyperlinked email address

Problem:

When I click on a hyperlinked name to send an email a different name comes up in the To: feild on the email.


For example

Joe.Smith@anywhere.com When I click on that the actual email that comes up goes to Jim_Jones@public.com

Indeed when I open edit hyperlink menu, the text to display and email address fields are different. I assume this occured from a copy format??

Anyway, anybody know an easy way to fix this, there are about 400 email addresses, and at least 20% to 40% of them are wrong. I really don't want to fix it by hand

Thanks

 

[Norie]

I think you might have to fix it 'by hand'.

How will Excel know what links are wrong and what they should be changed to?

Unless the links are based on some other data.

 

[Dan_WI]

It has something to do with formats. I think what caused the error is copy formats to different cells.

If this helps, I know if I copy paste value, the problem goes away. If you paste value, you lose the hyperlink format. I then manually make the cell a hyperlink again and it works fine. However, again I don't want to do this for 200+ cells.

So perhaps the better question might be, how do I force a bunch of cells to be formated as hyperlinks after having pasted them as values??

 

[Norie]

Have you tried recording a macro when you do it manually for a few instances?

Then you could adapt it to deal with multiple cells.

 

[Dan_WI]

Don't know why I didn't think of that originally. Thanks

This code worked, in case someone else has this issue

Range("e1").CurrentRegion.Select

Count = Selection.Rows.Count

c = 1

For c = 1 To Count
x1 = ActiveCell.FormulaR1C1
x2 = "mailto:" & x1
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
x2, TextToDisplay:=x1
ActiveCell.Offset(1, 0).Range("A1").Select
Next c

Thanks