Replace if the Nth Character in a text string is "I" then replace with "Y"

[Mohamedazees]

Dear Sir,

I Want a excel VBA code for replace if the Nth Character of Text strings is "I" the Please the character With "Y" in a Range of Cells

for Example i herewith attached the Sample data image for your ready reference.

Thanks in advance

 

[jolivanes]

Sub Maybe()
Dim a As Long, b As String, bb As String, c As Range
a = Application.InputBox(Prompt:="Position (nth character) of character.", Title:="Enter a number.", Type:=1)
b = Application.InputBox(Prompt:="Enter the alphabetical letter to be replaced.", Title:="Letter.", Type:=2)
bb = Application.InputBox(Prompt:="Enter the alphabetical letter that replaces it.", Title:="Letter.", Type:=2)
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Mid(c, a, 1) = b Then c.Value = Left(c, a - 1) & bb & Mid(c, a + 1, 99)
    Next c
End Sub

 

[Rick Rothstein]

For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  If Mid(c, a, 1) = b Then c.Value = Left(c, a - 1) & bb & Mid(c, a + 1, 99)
Next c

Inside the If..Then statement, you use Mid(c, a + 1, 99)... you can remove the third argument. Unlike in Excel formulas, the third argument to the Mid function is optional and, when omitted, returns all of the remaining text after the specified starting point.

 

[jolivanes]

Yes, you're right Rick. Now that you mention it, I think you taught me that previously and I had totally forgotten that.
Thanks for reminding me Rick. Appreciated.

 

[Mohamedazees]

Excellent Sir, it is working like a charm
Thanks your so much

 

[jolivanes]

Thanks for letting us know that it does what you want it to do.
Good luck