Add a letter to the value in cells in a certain column

[zack8576]

I have these excel files that contain either Yes or No in column E, and a text string in column D.
If column E on a row is Yes, AND if there is no letter J at the end of the string in column D, then J needs to be added to the end.
so in the screenshot below, the value in D8 needs to be F21223AJ, the value in D26 needs to be F21233AJ
my code below doesnt seem to work, and would you take the same approach to solve this problem ?

Sub StockPiecesWithHoleAddJ()
    Dim lr As Long, i As Long
    Set rng2 = Range("A1").CurrentRegion
    lr = rng2.Cells(Rows.Count, "D").End(3).Row
        
    For i = lr To 2 Step -1
        If rng2.Cells(i, 5) Like "Yes" And _
           Not rng2.Cells(i, 4) Like "*J*" Then
                rng.Cells(i, 4) = rng.Cells(i, 4) & "J"
        End If
    Next i
End Sub

 

[shinigamilight]

I didn't test it, you can try.

Sub StockPiecesWithHoleAddJ()
    Dim lr As Long, i As Long
    Set rng2 = Range("A1").CurrentRegion
    lr = rng2.Cells(Rows.Count, "D").End(3).Row
      
    For i = lr To 2 Step -1
        If rng2.Cells(i, 5).value = "Yes" And _
           Right(rng2.Cells(i, 4).value, 1) <> "J" Then
                Rng.Cells(i, 4) = Rng.Cells(i, 4) & "J"
        End If
    Next i
End Sub

 

[xlhelp15]

How about this,

Sub StockPiecesWithHoleAddJ()
Dim LRow, Inc As Long
LRow = Cells(Rows.Count, "D").End(xlUp).Row
For Inc = 2 To LRow
If Cells(Inc, 5).Value = "Yes" And Right(Cells(Inc, 4).Value, 1) <> "J" Then
Cells(Inc, 4).Value = Cells(Inc, 4).Value & "J"
End If
Next
End Sub

 

[zack8576]

I didn't test it, you can try.

Sub StockPiecesWithHoleAddJ()
    Dim lr As Long, i As Long
    Set rng2 = Range("A1").CurrentRegion
    lr = rng2.Cells(Rows.Count, "D").End(3).Row
     
    For i = lr To 2 Step -1
        If rng2.Cells(i, 5).value = "Yes" And _
           Right(rng2.Cells(i, 4).value, 1) <> "J" Then
                Rng.Cells(i, 4) = Rng.Cells(i, 4) & "J"
        End If
    Next i
End Sub

Thank you so much for the response.
I had a studid mistake in my code

rng.Cells(i, 4) = rng.Cells(i, 4) & "J"

should been

rng2.Cells(i, 4) = rng2.Cells(i, 4) & "J"

 

[zack8576]

Right(rng2.Cells(i, 4).value, 1) <> "J" Then

This part of the code means if there is no J in this cell ?

 

[zack8576]

How about this,

Sub StockPiecesWithHoleAddJ()
Dim LRow, Inc As Long
LRow = Cells(Rows.Count, "D").End(xlUp).Row
For Inc = 2 To LRow
If Cells(Inc, 5).Value = "Yes" And Right(Cells(Inc, 4).Value, 1) <> "J" Then
Cells(Inc, 4).Value = Cells(Inc, 4).Value & "J"
End If
Next
End Sub

thank you, you wouldnt recommend using code below ?

Not rng2.Cells(i, 4) Like "*J*" Then

can you tell me the advantage of using Right instead of Not Like ?

 

[xlhelp15]

This part of the code means if there is no J in this cell ?

Partially Yes, it checks with Right function and finding whether letter "J" is appended at the end of cell.

 

[xlhelp15]

thank you, you wouldnt recommend using code below ?

Not rng2.Cells(i, 4) Like "*J*" Then

can you tell me the advantage of using Right instead of Not Like ?

Here "*J*" means - before and after of "J" and it will check both the cases. If anyone keyed it incorrectly, then your macro will react in different way.

So, you would need to check at the right side whether letter "J" is present or not. For that case, we used that logic <> with Right() function.

 

[shinigamilight]

I polished the code & tested it now try

Sub StockPiecesWithHoleAddJ()
    Dim lr As Long, i As Long
    Set rng2 = Range("A1").CurrentRegion
    lr = rng2.Cells(Rows.Count, "D").End(3).Row
      
    For i = lr To 2 Step -1
        If UCase(rng2.Cells(i, 5).Value) = "YES" And UCase(Right(rng2.Cells(i, 4).Value, 1)) <> "J" Then
                rng2.Cells(i, 4).Value = rng2.Cells(i, 4).Value & "J"
        End If
    Next i
    
End Sub

 

[zack8576]

Here "*J*" means - before and after of "J" and it will check both the cases. If anyone keyed it incorrectly, then your macro will react in different way.

So, you would need to check at the right side whether letter "J" is present or not. For that case, we used that logic <> with Right() function.

this makes perfect sense ! thanks a bunch !
definitely writing this down in my VBA notebook !