Increment cell above value by 1

[paul3191]

Hi,

Help please, been trawling forums but cannot see a way of doing the following:

Raw data
A B
1234-5678
2345-6789
3456-7891
4567-8911

the first 4 digits never change, based on these numbers I need to do add numbers in Column A:

A B
2 1234-5678
3 2345-6789
4 3456-7891
4 4567-8911

The 3456-7891 & 4567-8911 will always have the same number, but that number will vary depending on if the 2345-6789 is present.
Below is a bigger snap shot of the data.

Raw data (The ------ is just to breakup the numbers for easier viewing in this forum and show a pattern)

A B A B
1234-5678 2 1234-5678
2345-6789 3 2345-6789
3456-7891 4 3456-7891
4567-8911 4 4567-8911
------------
1234-5678 2 1234-5678
3456-7891 3 3456-7891
4567-8911 3 4567-8911
------------
1234-5678 2 1234-5678
2345-6789 3 2345-6789
3456-7891 4 3456-7891
4567-8911 4 4567-8911

The code I have written so far is

Sub IF_statement()
Dim cell As Range
For Each cell In Range("b23:B100")
If Left(cell.Value, 4) = "1234" Then cell.Offset(0, -1) = 2
If Left(cell.Value, 4) = "2345" Then cell.Offset(0, -1) = 3
If Left(cell.Value, 4) = "3456" Then cell.Offset(0, -1) =
If Left(cell.Value, 4) = "4567" Then cell.Offset(0, -1) =
Next

End Sub

Hope someone can help, many thanks in advance.

 

[paul3191]

Sorry the table I tried to re-create removed all spaces when I saved thread.
A B----------A B
1234-5678 2 1234-5678
2345-6789 3 2345-6789
3456-7891 4 3456-7891
4567-8911 4 4567-8911
------------
1234-5678 2 1234-5678
3456-7891 3 3456-7891
4567-8911 3 4567-8911
------------
1234-5678 2 1234-5678
2345-6789 3 2345-6789
3456-7891 4 3456-7891
4567-8911 4 4567-8911

 

[korhan]

Here is a quick idea, sorry I just couldn't spend enough time on it but let me know if this helps you or not. Just run the macro and check out the results on the immediate window. Maybe you can morph this code into what you want.

Sub Test()
    Dim num1 As String
    Dim num2 As String
    Dim arr(0 To 8) As Integer
    For i = 0 To 8
        arr(i) = i + 1
    Next i
    
    
    For i = 1 To 9
        num1 = Empty
        For j = i To i + 3
            num1 = num1 + CStr(arr(j Mod 9))
        Next j
        Debug.Print num1
    Next i
    
End Sub

 

[korhan]

Ok. here is a better version, I am trying to understand the pattern 8911. What mine does is 8912. Let me know

Sub Test()
    Dim num1 As String
    Dim num2 As String
    Dim arr(0 To 8) As Integer
    For i = 0 To 8
        arr(i) = i + 1
    Next i
    
    
    For i = 1 To 5
        num1 = Empty
        num2 = Empty
        For j = i To i + 3
            num1 = num1 + CStr(arr(j Mod 9))
            num2 = num2 + CStr(arr((j + 4) Mod 9))
        Next j
        Debug.Print num1; "-"; num2
    Next i
    
End Sub

 

[paul3191]

Hi korhan,

Thanks for taking the time to look at this, as mentioned the first four numbers will always be the same, but the last 4 numbers are random.

A B-------------------A B
1234-xxxx---to -----2 1234-xxxx
2345-xxxx---------- 3 2345-xxxx
3456-xxxx---------- 4 3456-xxxx
4567-xxxx---------- 4 4567-xxxx
-------------
1234-xxxx---------- 2 1234-xxxx
3456-xxxx ----------3 3456-xxxx
4567-xxxx ----------3 4567-xxxx
-----------
1234-xxxx-----------2 1234-xxxx
2345-xxxx---------- 3 2345-xxxx
3456-xxxx-----------4 3456-xxxx
4567-xxxx---------- 4 4567-xxxx

I'll test your solution to see if I can make it work. thanks

 

[paul3191]

Solved my problem myself

Original code:
Sub IF_statement()
Dim cell As Range
For Each cell In Range("b23:B100")
If Left(cell.Value, 4) = "1234" Then cell.Offset(0, -1) = 2
If Left(cell.Value, 4) = "2345" Then cell.Offset(0, -1) = 3
If Left(cell.Value, 4) = "3456" Then cell.Offset(0, -1) =
If Left(cell.Value, 4) = "4567" Then cell.Offset(0, -1) =
Next

End Sub

New Code:
Sub IF_statement()
Dim cell As Range
For Each cell In Range("b23:B100")
If Left(cell.Value, 4) = "1234" Then cell.Offset(0, -1) = 2
If Left(cell.Value, 4) = "2345" Then cell.Offset(0, -1) = 3
If Left(cell.Value, 4) = "3456" Then cell.Offset(0, -1).select
If Left(cell.Value, 4) = "3456" Then cell.Offset(0, -1 = ActiveCell.Offset(-1, 0) + 1
If Left(cell.Value, 4) = "4567" Then cell.Offset(0, -1).select
If Left(cell.Value, 4) = "4567" Then cell.Offset(0, -1 = ActiveCell.Offset(-1, 0)
Next

End Sub