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]

Hi
What about

Sub test()
Dim a, x
Dim i&
With Sheets("DATABASE")
    a = .Cells(1, 10).Resize(.Cells(Rows.Count, 10).End(xlUp).Row)
    For i = 1 To 20
        x = Split(a(i, 1), "-")
        x(0) = Left(x(0), 5) & 1: x(1) = Left(x(0), 5) & 1
        a(i, 1) = Join(x, " - ")
    Next
    Cells(1, 10).Resize(UBound(a)) = a
    End With
End Sub

 

[ipbr21054]

Thanks,

Code in use is shown below.
I changed 10 for 11 as its column K not J

I run the code but get a run time error 9 Subscript out of range.
The following is shown in yellow

Private Sub CommandButton1_Click()
Dim a, x
Dim i&
With Sheets("DATABASE")
    a = .Cells(1, 11).Resize(.Cells(Rows.Count, 11).End(xlUp).Row)
    For i = 1 To 20
        x = Split(a(i, 1), "-")
        x(0) = Left(x(0), 5) & 1: x(1) = Left(x(0), 5) & 1
        a(i, 1) = Join(x, " - ")
    Next
    Cells(1, 11).Resize(UBound(a)) = a
    End With
End Sub

 

[mohadin]

Tis working here ok
Are all the cells in K have same structure like (987652 - 758651)??!!

 

[mohadin]

Or maybe your data start for K2?
If so

For i = 2 To 20

 

[mohadin]

Or may be you have an empty cells
Then try

Private Sub CommandButton1_Click()
Dim a, x
Dim i&
With Sheets("DATABASE")
    a = .Cells(1, 11).Resize(.Cells(Rows.Count, 11).End(xlUp).Row)
    For i = 1 To 20
    If a(i, 1) <> "" Then
        x = Split(a(i, 1), "-")
        x(0) = Left(x(0), 5) & 1: x(1) = Left(x(0), 5) & 1
        a(i, 1) = Join(x, " - ")
           End If
    Next
    Cells(1, 12).Resize(UBound(a)) = a
    End With
End Sub

 

[mohadin]

Any thing?

 

[ipbr21054]

Hi,
Before i change or test anything let me just recap.

Column K
First cell is row 6 then down the page, currently last row is 297 BUT will become longer as time goes on.
Number format is like 123456 - 654321
Some cells have no value at all.
Some cells have different numbers example 50418 or z123 or N/A or 2635415220147
This is why we concentrate ONLY on 123456 - 654321

I changed For i = 1 to For i - 2 but made no differnce & same code in yellow

 

[mohadin]

OOPs
my mistake

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)
    For i = 1 To UBound(a)
    If a(i, 1) <> "" Then
    On Error Resume Next
        x = Split(a(i, 1), "-")
        x(0) = Left(x(0), 5) & 1: x(1) = Left(x(0), 5) & 1
        a(i, 1) = Join(x, " - ")
           End If
    Next
    Cells(6, 11).Resize(UBound(a)) = a
    End With
End Sub

MODIFIED

 

[ipbr21054]

Hi,
Getting there but now the second part on the code is shown in yellow.

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)
    For i = 1 To UBound(a)
    If a(i, 1) <> "" Then
        x = Split(a(i, 1), "-")
        x(0) = Left(x(0), 5) & 1: x(1) = Left(x(0), 5) & 1
        a(i, 1) = Join(x, " - ")
           End If
    Next
    Cells(6, 11).Resize(UBound(a)) = a
    End With
End Sub

Ive been called out so back in 2 hours etc.
Many thanks for time so far