Find and Replace code help

Hello,
I am writing to seek help in implementing the following vba macro logic below:

If column E equals to “Auto”, “RMBS”, “CMBS”, “CLO”, “Student” or “Consumer” then do nothing,
Else
If there is any other value besides the one above THEN change it to “Esoteric”.

I am currently using the following find and replace code below but there are many variations in the large dataset and which is making it hard to manually input in the values into the code.

Sub find_replace()

Range("E1").Select
    Cells.Replace What:="Future flow ABS", Replacement:="Esoteric ABS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Cells.Replace What:="Infrastructure ABS", Replacement:="Esoteric ABS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

I have also attached workbook example with the dataset, indicating the yellow highlighted names, need to be changed into “Esoteric”.
example_set.xls

Please Note: the words such as Auto and many more mentioned above are not always in the same position in the cells

.

Any help with this issue, would be a great help.
Thanks in advance for your help and time.

Maybe:

Sub missy22()

Dim rcell As Range
Dim lr As Long

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 5).End(xlUp).Row

For Each rcell In Range("E1:E" & lr)

    If rcell <> "Auto" And rcell <> "RMBS" And rcell <> "CMBS" And rcell <> "CLO" And rcell <> "Student" And rcell <> "Consumer" Then
    
        rcell.ClearContents
        rcell.Replace "", "Esoteric", xlWhole
        
    End If
    
Next rcell

Application.ScreenUpdating = True
   
End Sub

Dear John,

Thank you for your response and suggestions. I am really sorry but the code also converts row 3 and rows 8-11. The code should only convert rows from 12 to 15 into "Esoteric" and leave the rest the cells in column E.

thank you for your help.

Originally Posted by jraj1106

Hi.

Sub FINDREPLACE()
    Dim rcnt As Long, colEstr As Variant
    
    rcnt = Range("E" & Rows.Count).End(xlUp).Row
    
    For i = 1 To rcnt
        colEstr = Trim(Range("E" & i).Text)
        MsgBox InStr(1, colEstr, "RMBS")
        If InStr(1, colEstr, "Auto") = 0 And InStr(1, colEstr, "RMBS") = 0 And InStr(1, colEstr, "CMBS") = 0 And InStr(1, colEstr, "CLO") = 0 And InStr(1, colEstr, "Student") = 0 And InStr(1, colEstr, "Consumer") = 0 Then
            Range("E" & i) = "Esoteric"
        End If
    Next
End Sub

Thank you so much for your code solution. The code works great. Thank you for your time and help.
I very grateful to your help.
Thanks a million.