Add text to existing text string

[William]

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

 

[Steve Yandl]

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

 

[Gord Dibben]

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