Finding Last Appearance Of Character In Cell, Replacing It With Another

[indi visual]

The following code finds the last appearance of "8" and deletes it, I was having trouble with modifying this code to find the last appearance of "8" and replacing it with "8}" instead. Also if I could get help with modifying the set range of this to search an entire column with an unknown length as well.


Dim c As Range

Set c = Range("A17")

For iCt = Len(c) To 1 Step -1

If Mid(c, iCt, 1) = "8" Then

c = Left(c, iCt - 1)

Exit For

End If

Next iCt

 

[Hui]

Indi


Firstly the last part of the question, Yesterday you posed a question which I answered and you acknowledged was correct. The code did exactly what you asked for again today


"could get help with modifying the set range of this to search an entire column with an unknown length as well."


Now to answer the whole post

[pre]

Sub Swap8()
Dim cn As Boolean
Dim c As Range

For Each c In Range("A2", Range("A2").End(xlDown))
For iCt = Len(c) To 1 Step -1
If Mid(c.Value, iCt, 1) = "8" Then
c.Value = Left(c.Value, iCt) + "}" + Right(c.Value, Len(c.Value) - iCt)
Exit For
End If
Next iCt
Next

End Sub

[/pre]

 

[indi visual]

Right on the money thanks Hui!


"For Each c In Range("A2", Range("A2").End(xlDown))" would have been my first guess. But I edited my post and added that last piece in case the range needed to be set a different way after someone responded (I wanted to prevent and add on "one more thing post").


In any case I thank you again Hui as always. For every question I post, there are 10 to 15 consecutive codes I independently write successfully based on the previous questions and answers of this forum (and I never post a question unless 3 to 4 hours on my own didn't go into my troubleshooting process first).


This site is awesome, and I'm learning a lot.