Function To Alter Text In All Cells

Question

I am wanting to scan column X and any cell that has a text length of > 11 - I want to pop up a message box asking the user to alter it. Once the user hits the okay button I then want to replace all of those values in column X. This is the syntax that I have, but it is not iterating each cell. Once I type in a new rename value the macro will continue to ask me, but it appears to not iterate the cells further down column X.

What must I change in order to have this macro function as I desire?

Function ShortenText()
Dim c As Range
Dim lRow As Long
Dim NewValue As String

    lRow = Cells(Rows.Count, 24).End(xlUp).Row

    For Each c In Range("X1:X" & lRow)
        Cells(c.Row, 2) = Len(c)
        If Len(c) > 11 Then
            NewValue = Trim(InputBox("What would you like to rename the value")
            Selection.Replace What:=c.Value, Replacement:=NewValue, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      End If
    Next c

End Function

Answer 1

I have tried your code. Make only a little modification :

Function ShortenText()
Dim c As Range
Dim lRow As Long
Dim NewValue As String

    lRow = Cells(Rows.Count, 24).End(xlUp).Row

    For Each c In Range("X1:X" & lRow)
        Cells(c.Row, 2) = Len(c)
        If Len(c) > 11 Then
            NewValue = Trim(InputBox("What would you like to rename the value"))
            c.Select
            Selection.Replace What:=c.Value, Replacement:=NewValue, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      End If
    Next c

End Function

Add "c.Select" and close the Trim function ")".