for each cell in a column that has text, add suffix "Jones"

[keithmct]

hello all, this one is pretty self-explanatory. I have a button I want to press and search range S14:S40 and if there is any text in there add something to the end like A or Jones.
that will enable me to redirect my spreadsheet to do an alternate calculation which I should have under control.

 

[alansidman]

Option Explicit


Sub Jones()
    Dim c As Range, rng As Range
    Set rng = Range("s14:s40")
    Application.ScreenUpdating = False
    For Each c In rng
        If Application.WorksheetFunction.IsText(c) Then
            c = c & " Jones"
        End If
    Next c
    Application.ScreenUpdating = True
    MsgBox "Actions Completed"
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub Jones()
  [S14:S50] = [IF(ISTEXT(S14:S50),S14:S50&" Jones",IF(S14:S50="","",S14:S50))]
End Sub[/td]
[/tr]
[/table]

 

[keithmct]

thank you both. I went with Rick's elegant suggestion solely due to length, although I understand Alan's much better.
If I was to change my button to a toggle button to revert to the original, i.e. take Jones off the end, what would that look like?

 

[Rick Rothstein]

If I was to change my button to a toggle button to revert to the original, i.e. take Jones off the end, what would that look like?

This macro code will toggle between adding Jones if it is not there and removing it if it is there...

[table="width: 500"]
[tr]
	[td]Sub Jones()
  [S14:S50] = [IF(RIGHT(S14:S50,6)=" Jones",LEFT(S14:S50,LEN(S14:S50)-6),IF(ISTEXT(S14:S50),S14:S50&" Jones",IF(S14:S50="","",S14:S50)))]
End Sub[/td]
[/tr]
[/table]

 

[keithmct]

love your work, Rick