Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cant scroll when editing cell (Excel 2007) | Excel Worksheet Functions | |||
Locked for Editing - Problem - Excel 2007 | Excel Discussion (Misc queries) | |||
Locked for editing problem with Excel 2007 beta | Excel Discussion (Misc queries) | |||
Problem Editing Macro in Shared Excel File | Excel Discussion (Misc queries) | |||
Problem Editing Macro in Shared Excel File | Excel Discussion (Misc queries) |