Replace last letter in column A using VBA

[aaronr]

I'm trying to replace the last letter in column A with out replacing any other letter using VBA.

Example - I'm trying to replace the last letter which is "A" in column A to be "B" with out changing any other letters

Column A

TAD106A

TAD107A

TAD108A

TAD109A

TA101A

TA102A

TA103A

TA104A

TA105A

<colgroup><col></colgroup><tbody>
</tbody>

Thanks for the help

 

[Michael M]

Maybe this and copy down in adjacent column....no VBA

=SUBSTITUTE(A2,RIGHT(A2,1),"B",2)

 

[L. Howard]

Give this a try. Copy to a standard module.

Requires the use of another column, I used column D. That D column can be changed to any other to suit your sheet, if it is now in use.

Howard

Option Explicit
Sub AARONR_A_B()
  Dim lRowCount&
 
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row - 1
  
  With Range("D2").Resize(lRowCount)
    .Formula = "=IF(RIGHT(A2,1)=CHAR(65),LEFT(A2,LEN(A2)-1)&""B"",A2)": .Value = .Value
    .Copy Range("A2")
  End With
  
  [D:D].ClearContents
  
End Sub

 

[aaronr]

L. Howard thanks for the code and works verify well. I'll added code delete that the whole column D when I'm done. Is there easy way to delete the whole Column D. Again thanks for your help.

 

[Rick Rothstein]

L. Howard thanks for the code and works verify well. I'll added code delete that the whole column D when I'm done. Is there easy way to delete the whole Column D. Again thanks for your help.

Here is a macro that does not involve any other columns...

[table="width: 500"]
[tr]
	[td]Sub ReplaceTrailingAwithB()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(RIGHT(@)=""A"",LEFT(@,LEN(@)-1)&""B"",@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[L. Howard]

You're welcome.

Be sure to check out Rick's code. No helper column needed.

Howard