Validate the letter "A" exist at the end and if doesn't put in

[aaronr]

I have group number and need to validate the letter "A" at the end and if doesn't exist need to put in. I currently us this code to rename and want to add a validate if the letter A is at the end then rename to B.

Dim Addr As String
Addr = "A2:A" & Cells(rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(RIGHT(@)=""A"",LEFT(@,LEN(@)-1)&""B"",@)", "@", Addr))

Thanks for the help

 

[NdNoviceHlp]

Dim r as range, Addr as range
Addr = "A2:A" & Cells(rows.Count, "A").End(xlUp).Row
For each r in Addr
If Right(r.value,1) = "A" then
r.value = Left(r.value, Len(r.value)-1)) & "B"
Else
r.value = r.value & "A"
end if
Next r

HTH. Dave

 

[aaronr]

Dave,
line 5 is Red in VBA and when i run it gives me compile error and Syntax Error.

Dim r as range, Addr as range
Addr = "A2:A" & Cells(rows.Count, "A").End(xlUp).Row
For each r in Addr
If Right(r.value,1) = "A" then
r.value = Left(r.value, Len(r.value)-1)) & "B"
Else
r.value = r.value & "A"
end if
Next r

HTH. Dave

 

[aaronr]

Dave,
I figure out the syntax error but still doesn't work

 

[FormR]

I have group number and need to validate the letter "A" at the end and if doesn't exist need to put in.

Hi, using a similar approach to what you are already using..

Dim Addr As String
Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(RIGHT(@)<>""A"",@&""A"",@)", "@", Addr))

 

[NdNoviceHlp]

My apologies for not posting tested code. FormR your code missed the requirement to change the "A" to a "B" if it is present. Tested this code and it seems to work, Dave

Sub test()
Dim r As Range, Addr As Range
Set Addr = Sheets("sheet1").Range("A2:A" _
       & Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
For Each r In Addr
MsgBox Right(r.Value, 1)
If Right(r.Value, 1) = "A" Then
r.Value = Left(r.Value, Len(r.Value) - 1) & "B"
Else
r.Value = r.Value & "A"
End If
Next r
End Sub

 

[FormR]

FormR your code missed the requirement to change the "A" to a "B" if it is present.

To be honest, I thought it was a new and separate request - maybe the OP will return and clarify at some point.