Change cell text , based on the text of other cell

[Vijayarc]

Hi Ninja Team

pleas help on my below query
I want to replace the text of 'I' Column based on text of 'N' column

my requirement is, in the attached sheet is
------------------------------------------------------
if N column which has text starts as "Rejected - * " then' I ' column text 'Reject" should change as" WIP-Credit"
else
the ' I ' column text 'Reject" should change as" WIP-LOB"

the below code is work only when I change column N as I -->I as N ,

Sub Replace_Text()

  Dim lastrow As Long, rng As Range
  
  lastrow = Cells(Rows.Count, "A").End(xlUp).row
  
  Application.ScreenUpdating = False
  
  With Sheet2
  .AutoFilterMode = False
  Set rng = .Range("A1:A" & lastrow)
  rng.AutoFilter field:=1, Criteria1:="Reje**"
  rng.Offset(1, 1).SpecialCells(12).Value = "Credit Reject"
  .AutoFilterMode = False
  
  End With
  
  Application.ScreenUpdating = True
  
  Set rng = Nothing

End Sub

 

[Belleke]

Hi try this.

Sub Belle()
    Dim lastrow As Long, i As Long
    lastrow = Range("N" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
        If Range("N" & i).Value Like "Rejected" & "*" Then
        Range("I" & i).Value = "WIP-Credit"
        Else
        Range("I" & i).Value = "WIP-LOB"
        End If
    Next i
End Sub

on a copy

 

[Vijayarc]

Hi Belle

thanks for try, but the result is wrong shows as below, else : changes all remaining text as Wip-Lob - i want
else
the remaining ' I ' column text 'Reject" should change as" WIP-LOB"



but i want my output as below image, i done the below code works as per logic, but i do no covert this with if else format code , my code is not in professional way -
Any alternate way if we can rewrite my below code

Sub Replace_Text()

    Dim lastrow As Long, rng As Range
 
    lastrow = Cells(Rows.Count, "I").End(xlUp).row
 
    Application.ScreenUpdating = False
 
    With Sheet2
        .AutoFilterMode = False
        Set rng = .Range("A1:N" & lastrow)
        rng.AutoFilter Field:=14, Criteria1:="Reje**"
    rng.AutoFilter Field:=9, Criteria1:="Rejected"
    Range("I2:I" & lastrow).Value = "WIP - Credit"
        .AutoFilterMode = False
     
         Set rng = .Range("A1:N" & lastrow)
         rng.AutoFilter Field:=9, Criteria1:="Rejected"
    Range("I2:I" & lastrow).Value = "WIP - LOb"
    End With
    Application.ScreenUpdating = True
 
    Set rng = Nothing

End Sub

 

[Vijayarc]

Hi Belle

the below modified code works great, thanks you so much

Sub Belle()
  Dim lastrow As Long, i As Long
  lastrow = Range("N" & Rows.Count).End(xlUp).Row
  For i = 2 To lastrow
  If Range("N" & i).Value Like "Rejected" & "*" Then
  Range("I" & i).Value = "WIP-Credit"
  End If
  If Range("I" & i).Value Like "Rejected" Then
  Range("I" & i).Value = "WIP-LOB"
  End If
  Next i
End Sub

 

[Belleke]

Glad to help.
Have a nice day.