Hyperlink trouble - 2000

Dan_WI

New Member
Joined
Sep 1, 2004
Messages
18
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 1, 2004
Messages
18
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 1, 2004
Messages
18
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top