Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinks off by 1 (Excel 2003 sp1)

    I inherited a spreadsheet from another group that I'll need to maintain the existing data and add new as needed. It has a list of employees and their pager numbers. The pager numbers are hyperlinks that when clicked on opens a new mail message and addresses it to the pager number. Unfortunately I discovered many of the cells refer to the wrong number. Some are correct, but the ones that aren't refer to the cell above. Ex: A1 displays 8778651788@skytel.com
    A2 displays 8884648692@skytel.com
    When you click on A2 it brings up a new message addressed to 8778651788@skytel.com, the value in A1. Is there any way other than doing them all manually to check if the hyperlink matches what the cell displays and if it doesn't to change it to that?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hyperlinks off by 1 (Excel 2003 sp1)

    Hi Patti,
    You could run the following macro:
    <pre>Sub fixLinks()
    Dim lnkCurrent As Hyperlink
    For Each lnkCurrent In ActiveSheet.Hyperlinks
    lnkCurrent.Address = "mailto:" & lnkCurrent.TextToDisplay
    Next lnkCurrent
    End Sub
    </pre>

    NB: this will do all hyperlinks in the sheet so if you have any other links that you don't want altered, you will have to change the code.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlinks off by 1 (Excel 2003 sp1)

    The following macro will make the e-mail address in each mail hyperlink equal to the displayed text:

    Sub RepairLinks()
    Dim lnk As Hyperlink
    For Each lnk In ActiveSheet.Hyperlinks
    If InStr(lnk.Address, "@") > 0 Then
    lnk.Address = "mailto:" & lnk.TextToDisplay
    End If
    Next lnk

    Alternatively, this macro will make the displayed text equal to the e-mail address (i.e. the reverse of the above version):

    Sub RepairLinks()
    Dim lnk As Hyperlink
    For Each lnk In ActiveSheet.Hyperlinks
    If InStr(lnk.Address, "@") > 0 Then
    lnk.TextToDisplay = Mid(lnk.Address, 8)
    End If
    Next lnk
    End Sub

    It's up to you to choose which one you prefer.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks guys

    You guys are fantastic!!! Thanks so much. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •