Apply 001 after customers name

[ipbr21054]

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

 

[ipbr21054]

Ive now got this to work.

Thanks

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

 

[Peter_SSs]

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

Wouldn't your code then leave you with two rows with STEVE MARTIN 001 since the previous code I suggested for you would have already created a STEVE MARTIN 001?

 

[ipbr21054]

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.

 

[Peter_SSs]

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

 

[ipbr21054]

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,

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

 

[Peter_SSs]

If you are happy, then I am happy.