Simple macro required to add 001 after a name please

[ipbr21054]

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.

 

[Joe4]

Try this:

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

 

[ipbr21054]

Worked great

Have a nice day

 

[Joe4]

You are welcome.

 

[Rick Rothstein]

@ipbr21054,

Given that you have a known, fixed range to evaluate, I thought you might find this one-liner macro to be of interest...

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

 

[ipbr21054]

@ipbr21054,

Given that you have a known, fixed range to evaluate, I thought you might find this one-liner macro to be of interest...

[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

 

[Joe4]

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.