Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

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" & Rows.Count).End(xlUp).Row ...

  1. #11
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    853

    Default 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
    Adam
    Adam James
    Enthusiastic Amateur

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  2. #12
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,293

    Default 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
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #13
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    Southampton, UK
    Posts
    2,454

    Default Re: Formula or Macro? If, Find, Len?

    Thank you both. Adam where in Southampton are you? I am in Southampton too!
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Windows 8.1 Excel 2007 Home

    Windows 7 Excel 2010 Work

  4. #14
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    853

    Default 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
    Adam
    Adam James
    Enthusiastic Amateur

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  5. #15
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    Southampton, UK
    Posts
    2,454

    Default 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.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Windows 8.1 Excel 2007 Home

    Windows 7 Excel 2010 Work

  6. #16
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    853

    Default Re: Formula or Macro? If, Find, Len?

    Quote Originally Posted by Dazzawm View Post
    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
    Adam
    Adam James
    Enthusiastic Amateur

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  7. #17
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    Southampton, UK
    Posts
    2,454

    Default Re: Formula or Macro? If, Find, Len?

    Quote Originally Posted by adam087 View Post
    Will drop you a PM next time we're headed out for some beer!

    Regards
    Adam
    Thanks, yes please do that.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Windows 8.1 Excel 2007 Home

    Windows 7 Excel 2010 Work

  8. #18
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    Southampton, UK
    Posts
    2,454

    Default 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
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Windows 8.1 Excel 2007 Home

    Windows 7 Excel 2010 Work

  9. #19
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    853

    Default 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
    Adma
    Adam James
    Enthusiastic Amateur

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  10. #20
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,293

    Default 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
    HTH, Peter
    Please test any code on a copy of your workbook.

Page 2 of 4 FirstFirst 1234 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com