Simple macro required to add 001 after a name please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
@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:
[table="width: 500"]
[tr]
	[td]Sub Add001()
  [B8:B881] = [IF(B8:B881="","",IF(ISNUMBER(-RIGHT(B8:B881)),B8:B881,B8:B881&" 001"))]
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
@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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Add001()
  [B8:B881] = [IF(B8:B881="","",IF(ISNUMBER(-RIGHT(B8:B881)),B8:B881,B8:B881&" 001"))]
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top