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]

Thanks again, VoG yours did it this time!

 

[Dazzawm]

This code works fine that was given in this thread but if the cell in column R is blank can the last 3 in column E be changed to 'ALL' please.

My first post explains what this code does. Thanks.

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 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))
Next i
End Sub

 

[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
        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))
    Else
        Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
        & "ALL"
    End If
Next i
End Sub

 

[Dazzawm]

Thanks but I get a runtime error 5 and it debugs here

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))

 

[VoG]

That would happen if the cell in column E contained less than 3 characters. Maybe

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
            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))
        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]

That would happen if the cell in column E contained less than 3 characters. Maybe

Thanks that seemed to work VoG.

 

[Dazzawm]

Hi VoG. I need a line of code added to the code in post 25 which is working well for me. If the data in column R is ATV/SUV then make the last 3 in column 'E' EST please. A bit like the cabrio - CON scenario. Thanks.

Once this other line of code is added like the cabrio line I should be able to see what I need to copy if another scenario arises and add myself.

 

[VoG]

Maybe this - I've just used an If rather than trying a nested Replace

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"
            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]

So if I need to add more could you highlight the lines I would need to copy and obviously change the values accordingly. Thanks.

 

[VoG]

I've added a made up example

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"
            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