Results 1 to 7 of 7

Thread: Simple macro required to add 001 after a name please

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Simple macro required to add 001 after a name please

    I would like a Macro to add 001 after a name.

    This will now be a one off press of a button then not used.
    Some info for you.
    Worksheet called POSTAGE
    Column B
    Range B8:B881

    I now need to add "SPACE 001" no quotes to All the names which do not have 002 003 004 etc after them.
    My list at present is like

    JOHN SMITH
    FRED WINTER
    FRED WINTER 002
    FRED WINTER 003
    ETC ETC

    So ONLY looking at names like in my example above JOHN SMITH would be changed to JOHN SMITH 001
    FRED WINTER would be changed to FRED WINTER 001

    So the new list will then start to look like,
    JOHN SMITH 001
    FRED WINTER 001
    FRED WINTER 002
    FRED WINTER 003
    ETC ETC

    This then sorts out my sheet & the new code we have just sorted will take control of starting with the new 001
    Otherwise i need to go through my list and manually add the 001 to every names of which is just text.

    Names like BOB JONES 002 will not be touched as it has 002 BUT if there is BOB JONES then please add 001 to it

    Many Thanks for your time.
    Saved me big time.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,770
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Simple macro required to add 001 after a name please

    Try this:
    Code:
    Sub InsertNums()
    
        Dim cell As Range
        
        Application.ScreenUpdating = False
        
        For Each cell In Range("B8:B881")
            If Len(cell) > 0 And Not IsNumeric(Right(cell, 3)) Then
                cell = cell & " 001"
            End If
        Next cell
        
        Application.ScreenUpdating = True
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple macro required to add 001 after a name please

    Worked great

    Have a nice day
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,770
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Simple macro required to add 001 after a name please

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,242
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Simple macro required to add 001 after a name please

    @ipbr21054,

    Given that you have a known, fixed range to evaluate, I thought you might find this one-liner macro to be of interest...
    Code:
    Sub Add001() [B8:B881] = [IF(B8:B881="","",IF(ISNUMBER(-RIGHT(B8:B881)),B8:B881,B8:B881&" 001"))] End Sub
    Last edited by Rick Rothstein; Jun 25th, 2019 at 02:44 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple macro required to add 001 after a name please

    Quote Originally Posted by Rick Rothstein View Post
    @ipbr21054,

    Given that you have a known, fixed range to evaluate, I thought you might find this one-liner macro to be of interest...
    Code:
    Sub Add001() [B8:B881] = [IF(B8:B881="","",IF(ISNUMBER(-RIGHT(B8:B881)),B8:B881,B8:B881&" 001"))] End Sub
    Thanks but i dont think i will need it anymore.
    Reason being i just had toclean up all the names that didnt have a number after them.
    Now my new code on another thread will add this.

    Can you advise why yours is different or what it would of done.

    My range was mentioned as thats as far as i have got down the page.

    Thanks
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,770
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Simple macro required to add 001 after a name please

    Can you advise why yours is different or what it would of done.
    It does the same thing, just a different way of doing.
    Often times, in Excel (and especially in VBA), there are many different ways to accomplish the same task.
    The methodologies are often different, but the results are often exactly the same.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

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
  •