Prepend and Append: add text to left or right of cell contents.

[darro]

I have this code (below) which does what I want, but is clearly limited in utility. How do I go about altering it so the new text can be added to any cells anywhere. At the moment it operates only on column D.

I'm not sure how to define any and all cells in vba yet?

There may even be a simpler method that I have missed. Hope you can help, there are a lot of cells waiting for me to add text to, I'm not looking forward to it if I have to do it manually.

(the cells are not all in same column and not all of one column needs to be amended hence my need for more flexibility)

Thanks for your help.

Sub AddText()
    Dim cell As Range
    Dim moretext As String
    Dim thisrng As Range
On Error GoTo endit
    whichside = InputBox("Add text to Left or right?")
    moretext = InputBox("Enter the text you want to add to cell")
Set thisrng = Intersect(ActiveSheet.Range("D:D"), ActiveSheet.Cells.SpecialCells(xlConstants))
    If whichside = "Left" Then
        For Each cell In thisrng
        cell.Value = moretext & cell.Value
    Next
    Else
        For Each cell In thisrng
        cell.Value = cell.Value & moretext
    Next
    End If
Exit Sub
endit:
    MsgBox "murrrggghhh"
End Sub

 

[darro]

Actually I have done it with this code:

Sub AddNewText()
    Dim cell As Range
    Dim moretext As String
    Dim thisrng As Range
On Error GoTo endit
    whichside = InputBox("Add text to Left or right?")
    moretext = InputBox("Enter the text you want to add to cell")
Set thisrng = Intersect(Selection, ActiveSheet.UsedRange)
    If whichside = "Left" Then
        For Each cell In thisrng
        cell.Value = moretext & cell.Value
    Next
    Else
        For Each cell In thisrng
        cell.Value = cell.Value & moretext
    Next
    End If
Exit Sub
endit:
    MsgBox "murrrggghhh"
End Sub