Sub Remp_A()
Dim i As Long, j As Byte, V As String, Suf As String
For i = 1 To Range("D" & Rows.Count).End(xlUp).Row
Suf = Right(Cells(i, 4), 3)
V = Cells(i, 4)
For j = 1 To 3
If Mid(Suf, j, 1) = "A" Then
Mid(V, Len(V) - 3 + j, 1) = "Z"
Cells(i, 4) = V
Exit For
End If
Next
Next
End Sub