Increment cell above value by 1

paul3191

New Member
Joined
Mar 10, 2006
Messages
35
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

paul3191

New Member
Joined
Mar 10, 2006
Messages
35
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

Board Regular
Joined
Nov 6, 2009
Messages
214
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.
Code:
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

Board Regular
Joined
Nov 6, 2009
Messages
214
Ok. here is a better version, I am trying to understand the pattern 8911. What mine does is 8912. Let me know
Code:
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

New Member
Joined
Mar 10, 2006
Messages
35
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
 
Last edited:

paul3191

New Member
Joined
Mar 10, 2006
Messages
35
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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
Top