Code advice to change number 1 to 2 in a specific column & its format

[ipbr21054]

Morning,

I am wanting to change a value of 1 to a 2 in two sets of numbers.

The column in question is J
The worksheet is called DATABASE
Range is J6 then down the page
Number format comprising of two sets of numbers in the cell is as example 987652 - 758651

The important part is the last number for each set of which must be a 1
As you can see in the above example the first set ends in 2 "987652" so once the code has been run it should become "987651"

Some info for you.

Some cells will have the correct format so this cell should be ignored.
Some cells will have both number sets ending in a 2 so both need to be changed to a 1
Some cells will only have a 2 in the second set of numbers.

Whilst im thinking about it the inserted values in each cell must be like this.
Fit set of numbers will always consist of 6 numbers followed by a space then - then a space then 6 numbers where both sets end with a 1

Is there something we can do to change the entered value should the user have used 2 spaces as opposed to 1 or the like.

Many thanks

 

[mohadin]

Just try post #9

 

[ipbr21054]

OK
Runing code #9 it kind of works but with issues BUT now no run time error.
This is what i see happen when i test.

N/A becomes N/A1

2232433421 becomes 223241

341152 - 433356 becomes 341151 - 341151

MANUAL CUT becomes MANU1

The items in question that i need changing has tis issue.
Example

123456 - 654321 the second set of numbers are deleted & replaced with the same set of first number so 123456 - 123456

Like wise 999999 - 555555 becomes 999999 - 999999

 

[mohadin]

HI
Hope this covers all

Private Sub CommandButton1_Click()
    Dim a, x
    Dim i&
    With Sheets("DATABASE")
        a = .Cells(6, 11).Resize(.Cells(Rows.Count, 11).End(xlUp).Row - 5).Value2
        For i = 1 To UBound(a)
            If a(i, 1) <> "" Or a(i, 1) <> "N/A" Then
                On Error Resume Next
                x = Split(a(i, 1), " - ")
                If Len(x(0)) = 6 Then
                    If Right(x(0), 1) = 2 Then x(0) = Left(x(0), 5) & 1
                End If
                    If Len(x(1)) = 6 Then
                        If Right(x(1), 1) = 2 Then x(1) = Left(x(1), 5) & 1
                    End If
            End If
            a(i, 1) = Join(x, " - ")
        Next
        Cells(6, 11).Resize(UBound(a)) = a
    End With
End Sub

 

[ipbr21054]

Thanks for your time with this, now all good.

Have a nice day

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy and safe