Formula or Macro? If, Find, Len?

[Dazzawm]

I have column E. Inside it is various length text but I need it to concentrate on the last 3 digits. I need it to replace it with the first 3 characters in column R.

e.g column E may have NIL_ALL in it so I need it to look and see if anything is in column R and if there is replace 'ALL' with the first 3 in R, which may be Hatch so it will become NIL_HAT in E. If there is nothing in R (in the same row of course) then do nothing. A macro would be better where it would replace what is in E, however I could put a helper column in with a formula. Thanks.

Just to confuse things if the word cabrio is found in R then I need the 3 letters to be CON not CAB, if this provides a problem I can do a find/replace after.

 

[Dazzawm]

Hi. I have added the rows in yellow but it gets an error on the 'else' coloured orange. Any ideas please?

Sub MyMacro18()
Dim lr As Long, i As Long
lr = Range("E" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If Range("R" & i).Value <> "" Then
If Len(Range("E" & i).Value) >= 3 Then
If Range("R" & i).Value = "ATV/SUV" Then
Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
& "EST"
'ElseIf Range("R" & i).Value = "XXX/YYY" Then
'Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
'& "ZZZ"
ElseIf Range("R" & i).Value = "ATV" Then
Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
& "EST"
End If
ElseIf Range("R" & i).Value = "SUV" Then
Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
& "EST"
End If
Else
Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
& UCase(Left(Replace(Range("R" & i).Value, "Cabrio", "CON"), 3))
End If
Else
Range("E" & i).Value = Range("R" & i).Value
End If
Else
If Len(Range("E" & i).Value) >= 3 Then
Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) & "ALL"
Else
Range("E" & i).Value = "ALL"
End If
End If
Next i
End Sub

I just need it foolproof so I can add lines myself when I come across any eventuality instead of bothering you more.

 

[VoG]

Try

Sub MyMacro18()
Dim lr As Long, i As Long
lr = Range("E" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
    If Range("R" & i).Value <> "" Then
        If Len(Range("E" & i).Value) >= 3 Then
            If Range("R" & i).Value = "ATV/SUV" Then
                Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
                & "EST"
            ElseIf Range("R" & i).Value = "ATV" Then
                Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
                & "EST"
            ElseIf Range("R" & i).Value = "SUV" Then
                Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
                & "EST"
            Else
                Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
                & UCase(Left(Replace(Range("R" & i).Value, "Cabrio", "CON"), 3))
            End If
        Else
            Range("E" & i).Value = Range("R" & i).Value
        End If
    Else
        If Len(Range("E" & i).Value) >= 3 Then
            Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) & "ALL"
        Else
            Range("E" & i).Value = "ALL"
        End If
    End If
Next i
End Sub

 

[Dazzawm]

Thanks Vog, I think my thread is victim of some spamming.

 

[VoG]

I think my thread is victim of some spamming.

I've taken care of that