Using Excel 2000. In a VBA macro, I want to copy the contents of a
cell containing text and add that data to the end of a text string in
the activeCell without overwriting the existing data, then move down 1
row and repeat until a blank cell is found.
I have tried using copy then enter edit mode, but paste is not
available in edit mode.
Is there a bit of code that I can use to accomplish this? Would very
much appreciate your input. William

If the text to be added to the end of the active cell and non empty cells
below is in "A1" then this would do what you're asking.

Sub EditByConcatenation()
Dim rngEdit As Range
Set rngEdit = ActiveCell
Do Until rngEdit.Value = ""
rngEdit.Value = rngEdit.Value & Range("A1").Value
Set rngEdit = rngEdit.Offset(1, 0)
Loop
End Sub


Steve Yandl


"William" wrote in message
ups.com...
Using Excel 2000. In a VBA macro, I want to copy the contents of a
cell containing text and add that data to the end of a text string in
the activeCell without overwriting the existing data, then move down 1
row and repeat until a blank cell is found.
I have tried using copy then enter edit mode, but paste is not
available in edit mode.
Is there a bit of code that I can use to accomplish this? Would very
much appreciate your input. William

Or a refinement that allows selecting the start cell for the fill.

Sub Add_Copied_Text()
Dim cell As Range
Dim moretext As String
Dim thirngEdit As Range
Dim rngEdit As Range
'select a cell with text to copy
Set currentSelection = Application.ActiveCell
currentSelection.Name = "oldrange"
moretext = Range("oldrange").Value
'select a cell to start the fill
Set rngEdit = Application.InputBox(prompt:= _
"Select a cell", Type:=8)
Do Until rngEdit.Value = ""
rngEdit.Value = rngEdit.Value & Range("oldrange").Value
Set rngEdit = rngEdit.Offset(1, 0)
Loop
End Sub


Gord Dibben MS Excel MVP

On Sun, 11 Feb 2007 16:26:03 -0800, "Steve Yandl"
wrote:

If the text to be added to the end of the active cell and non empty cells
below is in "A1" then this would do what you're asking.

Sub EditByConcatenation()
Dim rngEdit As Range
Set rngEdit = ActiveCell
Do Until rngEdit.Value = ""
rngEdit.Value = rngEdit.Value & Range("A1").Value
Set rngEdit = rngEdit.Offset(1, 0)
Loop
End Sub


Steve Yandl


"William" wrote in message
oups.com...
Using Excel 2000. In a VBA macro, I want to copy the contents of a
cell containing text and add that data to the end of a text string in
the activeCell without overwriting the existing data, then move down 1
row and repeat until a blank cell is found.
I have tried using copy then enter edit mode, but paste is not
available in edit mode.
Is there a bit of code that I can use to accomplish this? Would very
much appreciate your input. William