Macros

[David]

I want to add a line before and after text in a cell and
as I have over 300 cells have tried creating a macro
using the following:
Start recording
F2 to position the cursor in the cell
Ctrl+Home to put the cursor at the front of the cell
Alt+Enter to insert a line
Ctrl+End to move to the end of the text
Alt+Enter to insert a line
Enter to move to the next cell
Stop recording

However, the macro inserts the same text in every cell on
which I run the macro when I want it to ignore the text
and simply add a line before and after the existing text.
How do I get it to ignore the text in the cell? Couldn't
find the Relative Cell Ref button in Excel 2000.

 

[JC]

This macro will insert a blank row above and below the
current cell, but for only the selected cell. You would
have to expand the macro if you wanted to do it for all
300 cells in that column.

Sub MacroName()
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
ActiveCell.EntireRow.Insert
End Sub

 

[David]

I did not want to put a row either side of the cell but a
line either side of the test within a cell as I have
check boxes in other cells
Thanks for your help
David

 

[Bernie Deitrick]

David,

Select the 300 cells, then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub DavidsMacro()
Dim myCell As Range

For Each myCell In Selection
myCell.Value = "" & Chr(10) & myCell.Value & Chr(10) & ""
Next myCell
End Sub

 

[Guest]

Many thanks - this worked and saved me a lot of time.
David

 

[JC]

Sorry,
Try this. Again this does it for the active cell only.
You will have to create a simple loop to have it do the
entire row or column.
JC

Sub MacroName()
Dim CellValue
CellValue = ActiveCell.Value
ActiveCell.FormulaR1C1 = "" & Chr(10) & CellValue &
Chr(10) & ""
End Sub