Excel 2007 macro problem in editing a cell
 

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

Excel 2007 macro problem in editing a cell
 

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

I would just use editreplace 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 editreplace steps and insert them in the
MakeHyperlinks macro.


Gord Dibben MS Excel MVP

On Tue, 16 Sep 2008 15:40:01 -0700, pjudge
wrote:

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

Excel 2007 macro problem in editing a cell
 

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

"Gord Dibben" wrote:

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

I would just use editreplace 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 editreplace steps and insert them in the
MakeHyperlinks macro.


Gord Dibben MS Excel MVP

On Tue, 16 Sep 2008 15:40:01 -0700, pjudge
wrote:

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