# Formula or Macro? If, Find, Len?

This is a discussion on Formula or Macro? If, Find, Len? within the Excel Questions forums, part of the Question Forums category; Sorry. I was being malcoordinated. Code: Sub test() Dim LR As Long, i As Long LR = Range("E" &amp; Rows.Count).End(xlUp).Row ...

1. ## Re: Formula or Macro? If, Find, Len?

Sorry. I was being malcoordinated.
Code:
```Sub test()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = 1 To LR
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```
Regards

2. ## Re: Formula or Macro? If, Find, Len?

Try

Code:
```Sub test()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = 1 To LR
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```

3. ## Re: Formula or Macro? If, Find, Len?

Thank you both. Adam where in Southampton are you? I am in Southampton too!

4. ## Re: Formula or Macro? If, Find, Len?

Small world hey. I live by Dock Gate 4 (the actual dock gate 4, not the nightclub). I work at the moment in Chandler's Ford although I'm changing job next week to work between Romsey & Winchester.

You?

Regards

5. ## Re: Formula or Macro? If, Find, Len?

Sorry I noticed one slight error. It needs to start in row 2 and not affect the headers in row 1.

I work in Portswood and live in Shirley.

6. ## Re: Formula or Macro? If, Find, Len?

Originally Posted by Dazzawm
Sorry I noticed one slight error. It needs to start in row 2 and not affect the headers in row 1.

I work in Portswood and live in Shirley.
Change the loop so i starts at 2.

Will drop you a PM next time we're headed out for some beer!

Regards

7. ## Re: Formula or Macro? If, Find, Len?

Will drop you a PM next time we're headed out for some beer!

Regards

8. ## Re: Formula or Macro? If, Find, Len?

Hi again Adam/Vog. Could the code work in the reverse. If the last 3 in E are the following can the words be put in R?

E R

HAT - Hatch
SAL - Saloon
COU - Coupe
EST - Estate
VAN - Van
MPV - MPV
CON - Cabrio

9. ## Re: Formula or Macro? If, Find, Len?

Yeah. I'd drop them into a lookup table somewhere then reference them at your leisure.
[R2] =VLOOKUP(RIGHT(E2, 3), MyLookupSheet!\$A\$2:\$B\$8, 2, False)

Regards

10. ## Re: Formula or Macro? If, Find, Len?

Try

Code:
```Sub test()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Select Case Right(Range("E" & i).Value, 3)
Case "HAT": Range("R" & i).Value = "Hatch"
Case "SAL": Range("R" & i).Value = "Saloon"
Case "COU": Range("R" & i).Value = "Coupe"
Case "EST": Range("R" & i).Value = "Estate"
Case "Van": Range("R" & i).Value = "Van"
Case "MPV": Range("R" & i).Value = "MPV"
Case "CON": Range("R" & i).Value = "Cabrio"
End Select
Next i
End Sub```

Page 2 of 4 First 1234 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•