Apply 001 after customers name

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Hi,

I have a worksheet called POSTAGE.
There are customers names in column B
Current range at present is B10:B979

I would like some help with a basic code where any customer that doesnt have a number after there name then the code would add 001

So TOM JONES would then become TOM JONES 001

CLINT EASTWOOD 001 STEVE MARTIN 002 etc etc would be left untouched

Many thanks
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Ive now got this to work.

Thanks


Code:
Sub InsertNumber001()
 
    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
The code I mentioned above should of only changed the names without numbers after.

So if there was STEVE MARTIN 001 it should have not touched it.

If there was say STEPHEN. MARTIN then it should of made it STEPHEN MARTIN 001.


Now I’m doubting myself so tomorrow I will need to check.
 
Upvote 0
The code I mentioned above should of only changed the names without numbers after.

So if there was STEVE MARTIN 001 it should have not touched it.
I agree, but with your example in post 1 in this thread there was a STEVE MARTIN 002 which means there must also have already been a STEVE MARTIN and a STEVE MARTIN 001

If your code changes the STEVE MARTIN to STEVE MARTIN 001 that will give you two of STEVE MARTIN 001
 
Upvote 0
Morning,
I see what you are saying.

You are saying that,
STEVE MARTIN
STEVE MARTIN 001
STEVE MARTIN 002

Will then become,
STEVE MARTIN 001
STEVE MARTIN 001
STEVE MARTIN 002

Ive used this code to check but all seems ok,

Code:
Private Sub CustomerDuplicateSearch_Click()

Dim Cell As Variant
Dim Source As Range


Set Source = Range("B8:B5000")


For Each Cell In Source
    
If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
        
Cell.Interior.Color = RGB(0, 255, 0)
        
    End If
Next


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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