If the last character in a cell is "A", delete that character from the cell-CodePudding

Worksheets("1").Activate

  Dim LR As Long
  LR = Cells(Rows.Count, "D").End(xlUp).Row
  Range("D2:D" & LR) = Evaluate(replace("IF(D2:D#=""A"",""A"",LEFT(D2:D#,FIND(A|A,SUBSTITUTE(D2:D#,RIGHT(TRIM(D2:D#)),A|A,LEN(D2:D#)-LEN(SUBSTITUTE(D2:D#,RIGHT(TRIM(D2:D#)),""A""))))))", "#", LR))
End Sub

I've been trying to rework this code to loop through column D until end, and delete any "A" characters from the cells, if that "A" is found at the end.

So far I'm just returning a #VALUE.

Thanks for any input.

CodePudding user response：

I expect that you could write a formula to do this (with "evaluate") but it's pretty straightforward with pure VBA:

Sub chomp_A()
  Dim row As Long
  Dim LR As Long
  LR = Cells(Rows.Count, "D").End(xlUp).row
  For row = 2 To LR
    Do While Right(Cells(row, "D").Value, 1) = "A"
      Cells(row, "D").Value = Left(Cells(row, "D").Value, Len(Cells(row, "D").Value) - 1)
    Loop
  Next
End Sub

This will remove all trailing "A" characters from text in column D. So "SPARTA" would become "SPART" and "RUFIYAA" woudl become "RUFIY"

CodePudding user response：

For sake of alternatives:

Sub Test()

Dim str As String: str = "TEST BAAAA"

'Option 1:
Debug.Print Replace(Replace(RTrim(Replace(Replace(str, " ", "|"), "A", " ")), " ", "A"), "|", " ")

'Option 2:
With CreateObject("vbscript.regexp")
    .Pattern = "^(.*?)A*$"
    Debug.Print .Replace(str, "$1")
End With

End Sub