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

 

[Kenneth Hobson]

Are all the cells in the Selection unlocked?

 

[Mr.Piccolo22]

Are all the cells in the Selection unlocked?

I did double check that and unlocked the cells when I selected all the cells. I even locked all the selected cells and unlocked again.

Thank you for your response!

 

[Fluff]

Do the cells contain text, as opposed to numbers or formulae?

 

[Kenneth Hobson]

I can not duplicate a problem. I did change the set range a bit.

I would suggest a Select Case rather than an If/Else. e.g. If 1 is not set in first inputbox and it is a number, the Else executes as it is now.

As Fluff said, If numbers in the cell, nothing happens in them.

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)
  If whichside = 1 Then
    For Each cell In thisrng
      cell.Value = moretext & cell.Value
    Next cell
    Else
    For Each cell In thisrng
      cell.Value = cell.Value & moretext
    Next cell
  End If
  Exit Sub
  
endit:
  MsgBox "only formulas in range"
End Sub

 

[Mr.Piccolo22]

Do the cells contain text, as opposed to numbers or formulae?

The cells contain numbers and letters, no formulas.

 

[Mr.Piccolo22]

The manufacturer uses both (numbers and letters) to start their part numbering system.

 

[Fluff]

Are any of the values purely numbers?

 

[Mr.Piccolo22]

The simple answer is yes.

 

[Fluff]

Your code will only look at text values, not numbers.
Do you want to add the prefix/suffix to numbers as well as text?