Excel 2007 macro problem in editing a cell

[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.

 

[Gord Dibben]

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

 

[pjudge]

Thanks. You have saved me a lot of time. I wish I had asked earlier!