Find replace a name with a hyperlink

[MelanieB]

I need to search an excel spreadsheet and replace all instances of a person's name with that person's email address and have it show as hyperlink, not text. How do I get it to show as a hyperlink? This is just one person and one email address, but her name is all over the place in this large worksheet so I don't want to have to do it manually.

[HansV]

Where does the e-mail address come from? Is it available in a cell, or do you want a macro that prompts for it, or do you want to hard-code the e-mail address in the macro?

[MelanieB]

Good question. The email address comes from.. hmm .. well we have to put it in. It does't come from anywhere at this point. It's not already in any other cell.

My first thought is to ask for a one-time prompt for the email address. But, it would also work to have it hard coded into the macro.

[HansV]

Try this:

Code: Select all

Sub AddHyperlinks()
  Dim strName As String
  Dim strEmail As String
  Dim wsh As Worksheet
  Dim rng As Range
  Dim strAddress As String
  strName = InputBox("What name do you want to search for?")
  If strName = "" Then
    MsgBox "No name provided", vbExclamation
    Exit Sub
  End If
  strEmail = InputBox("What is the e-mail address?")
  If strEmail = "" Then
    MsgBox "No e-mail address entered", vbExclamation
    Exit Sub
  End If
  Set wsh = ActiveSheet
  Set rng = wsh.Cells.Find(What:=strName, LookIn:=xlValues, _
    LookAt:=xlWhole)
  If Not rng Is Nothing Then
    strAddress = rng.Address
    Do
      wsh.Hyperlinks.Add Anchor:=rng, _
        Address:="mailto:" & strEmail, _
        TextToDisplay:=rng.Value
      Set rng = wsh.Cells.FindNext(After:=rng)
    Loop While rng.Address <> strAddress
  End If
End Sub

[MelanieB]

That's so slick Hans! Thank you!! It's perfect!

[MelanieB]

Actually, that worked when I tested it using Excel 2010. It didn't work when I used it in Excel 2003. And, the user I'm helping has Excel 2003. Sorry for leaving that bit of info out.

I attached a screenshot of the compile error. I don't know what this means or how to fix it.

[HansV]

The code works in all versions of Excel, but if you look closely, you'll see that somehow the copy/paste process has inserted a blank line between every pair of code lines. This is not your fault, it's something that the browser does.
For many lines it doesn't matter, but the lines that end in an underscore (the so-called line continuation) expect that the code continues on the line immediately below. That is why several lines are highlighted in red. If you remove the blank lines, it should be OK again.

[MelanieB]

Ah ha. That did it. Thank you!