Excel 2007 macro problem in editing a cell

  • Thread starter Thread starter pjudge
  • Start date Start date
P

pjudge

I have a column of web addresses, e.g. www.anywhere1.com, www.anywhere2.com.
etc.
I need to edit these to contain an email address of info@ the original URL,
e.g. (e-mail address removed), (e-mail address removed), etc.
When I replicate the manual steps that work by using the macro recorder with
relative positions this way:
"F2, Home, delete, delete, delele, delete, info@, enter" it does not work.
Running that macro simply populates the new cell with the email address of
the cell I used for recording, e.g. (e-mail address removed), and it is formatted
as text only, not an email link.
I don't know VB so I can't edit the macro script and am simply lost as to
why the recording fails to give the same results as the manual steps.


Thanks in advance.
 
You cannot record or run a macro while in Edit Mode so forget that method.

I would just use edit>replace on the column.

Replace www with info@

Then run this macro on the column.

Sub MakeHyperlinks()
'David McRitchie
Dim Cell As Range
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add anchor:=Cell, _
Address:=Cell.Value, _
ScreenTip:=Cell.Value, _
TextToDisplay:=Cell.Value
End With
Next Cell
End Sub

You could record the edit>replace steps and insert them in the
MakeHyperlinks macro.


Gord Dibben MS Excel MVP
 
Back
Top