I have a string of text in a single cell.

I need a macro to add the following to the end of the string:
1) word "(STOP)" and then ALT Enter {key stoke} to add carriage return.

ISSUE: The macro below adds the word "(STOP)" at the end of the string ,
but I need it to also add a "carriage retun" at the end of the string.

Sub EDIT()
'
' EDIT Macro
'
' this macro creates a name "stop" at the current cell location and then
copies the contents to a range name "copyto". At the stop location the macro
ref to "copyto" and then joins it with "(stop)" and finishes with paste
special values. NEED to add "Carriage Return" to this macro.
'
ActiveWorkbook.Names("stop").Delete
ActiveWorkbook.Names.Add Name:="STOP", RefersToR1C1:=ActiveCell
' "=Sheet1!R5C5"
Selection.Copy
Application.Goto Reference:="copyto"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="STOP"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)"""
Range("STOP").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Change
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)"""
to
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)""" & vbLf

Hope this helps,

Hutch

"lightjag" wrote:

I have a string of text in a single cell.

I need a macro to add the following to the end of the string:
1) word "(STOP)" and then ALT Enter {key stoke} to add carriage return.

ISSUE: The macro below adds the word "(STOP)" at the end of the string ,
but I need it to also add a "carriage retun" at the end of the string.

Sub EDIT()
'
' EDIT Macro
'
' this macro creates a name "stop" at the current cell location and then
copies the contents to a range name "copyto". At the stop location the macro
ref to "copyto" and then joins it with "(stop)" and finishes with paste
special values. NEED to add "Carriage Return" to this macro.
'
ActiveWorkbook.Names("stop").Delete
ActiveWorkbook.Names.Add Name:="STOP", RefersToR1C1:=ActiveCell
' "=Sheet1!R5C5"
Selection.Copy
Application.Goto Reference:="copyto"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="STOP"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)"""
Range("STOP").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Tom,

I could not get &vbLF to work so I got the desired result by ref a cell ,
named "return" with "STOP and ALTENTER {key stroke}, with:

ActiveCell.FormulaR1C1 = "=copyto&RETURN"

Also the cells I am applying this macro to already are pre-formated for text
wrap.

"Tom Hutchins" wrote:

Change
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)"""
to
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)""" & vbLf

Hope this helps,

Hutch

"lightjag" wrote:

I have a string of text in a single cell.

I need a macro to add the following to the end of the string:
1) word "(STOP)" and then ALT Enter {key stoke} to add carriage return.

ISSUE: The macro below adds the word "(STOP)" at the end of the string ,
but I need it to also add a "carriage retun" at the end of the string.

Sub EDIT()
'
' EDIT Macro
'
' this macro creates a name "stop" at the current cell location and then
copies the contents to a range name "copyto". At the stop location the macro
ref to "copyto" and then joins it with "(stop)" and finishes with paste
special values. NEED to add "Carriage Return" to this macro.
'
ActiveWorkbook.Names("stop").Delete
ActiveWorkbook.Names.Add Name:="STOP", RefersToR1C1:=ActiveCell
' "=Sheet1!R5C5"
Selection.Copy
Application.Goto Reference:="copyto"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="STOP"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)"""
Range("STOP").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)""&char(10)"

I don't quite understand what you're doing, but it looks like you're putting the
activecell's value in the cell named copyto.

Then you're taking the value and putting "(stop) & alt-enter" in the activecell.

If that's the case:

Option Explicit
Sub testme01()
With ActiveCell
ActiveWorkbook.Names("CopyTo").RefersToRange.Cells (1).Value = .Value
.Value = .Value & " (STOP)" & vbLf
'.Name = "STOP"
End With
End Sub

There's no reason you need to name the range STOP in this code, but maybe you
use it for something else???

lightjag wrote:

I have a string of text in a single cell.

I need a macro to add the following to the end of the string:
1) word "(STOP)" and then ALT Enter {key stoke} to add carriage return.

ISSUE: The macro below adds the word "(STOP)" at the end of the string ,
but I need it to also add a "carriage retun" at the end of the string.

Sub EDIT()
'
' EDIT Macro
'
' this macro creates a name "stop" at the current cell location and then
copies the contents to a range name "copyto". At the stop location the macro
ref to "copyto" and then joins it with "(stop)" and finishes with paste
special values. NEED to add "Carriage Return" to this macro.
'
ActiveWorkbook.Names("stop").Delete
ActiveWorkbook.Names.Add Name:="STOP", RefersToR1C1:=ActiveCell
' "=Sheet1!R5C5"
Selection.Copy
Application.Goto Reference:="copyto"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="STOP"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=copyto&"" (STOP)"""
Range("STOP").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

--

Dave Peterson