VBA Code not filling in complete range

[Mr.Piccolo22]

I found the following code and it has worked for years, however today it stopped and I am not sure why.

It will add let's X in front of the number I wanted to add but now it will only add it to a majority of cells and then the rest leave alone. When I do select the cells that didn't change and try to run it again, it gives me and only in range error.

I have Windows 10, and Excel 365 if that helps.

Sub Add_Text()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
If whichside = 1 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 "only formulas in range"
End Sub

I am not sure what broke and still learning VBA. Thank you for any help.

Mr.P

 

[Mr.Piccolo22]

If I am understanding you correctly, yes.

so if the vendor number is 1234 then after the maco would be x1234,

or

Pre Macro: a1234, post macro XA1234

And what I add could be a combination of numbers, letters, or symbols. Like "AB-" "A1-", ect.

 

[Kenneth Hobson]

To do both, maybe:

Sub Add_Text()
  Dim cell As Range, thisrng As Range
  Dim moretext As String, whichside As Integer
  
  On Error GoTo endit
  whichside = InputBox("Left = 1 or Right =2")
  moretext = InputBox("Enter your Text")
  
  Set thisrng = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
  Set thisrng = Union(thisrng, Selection.SpecialCells(xlCellTypeConstants, xlNumbers))
  Select Case whichside
    Case 1
      For Each cell In thisrng
        cell.Value = moretext & cell.Value
      Next cell
    Case 2
      For Each cell In thisrng
        cell.Value = cell.Value & moretext
      Next cell
    Case Else
  End Select
  Exit Sub
  
endit:
  MsgBox "only formulas in range"
End Sub

 

[Mr.Piccolo22]

Thank you so much! I will try it and let you know!

 

[Mr.Piccolo22]

Kenneth Hobson - That worked!!! Thank you so much for your time and help.

Fluffy - Thank you as well for your time!

You just save me days/hours!!

Mr.P