Continues number issue (1 Viewer)

JoseAcosta

New member
Local time
Today, 13:40
Joined
Aug 2, 2018
Messages
5
Hi,
This will be my first question.

I need to have in a excellent sheet 2 columns;

Column 1 will be continue numbers like;
1000
1001
1002
1003
etc....

Column 2 is the tricky one;

will be incremental like this:
001-0
001-2
001-3
001-4
etc...
but when it gets to 001-9 have to change to
002-1 and then....
002-2
002-3
002-4
until it reaches
999-9

column 1 will be increasing also but I have no problem is just make column 2 to increase and loop till it reaches 999-9

Thanks in advance for all your help
 

June7

AWF VIP
Local time
Today, 08:40
Joined
Mar 9, 2014
Messages
5,423
You have an Excel sheet? Code in Sheet1 code module like:

Code:
Sub GenerateNums()
Dim x As Integer, y As Integer, z As Integer
z = 1
For x = 1 To 999
    For y = 1 To 9
        Cells(z, 1) = Format(x, "000") & "-" & y
        z = z + 1
    Next
Next
End Sub
 

JoseAcosta

New member
Local time
Today, 13:40
Joined
Aug 2, 2018
Messages
5
Thanks work perfect but now I have another one regarding this one.

How can I make to start at one specific cell?

I run the code and go to cell A1 I need to make it to go B2 Row one instead, If I also want to call this function to do the same in another Column how will be?
 

June7

AWF VIP
Local time
Today, 08:40
Joined
Mar 9, 2014
Messages
5,423
Sorry, missed the column A requirement.

Code:
Sub GenerateNums()
Dim r As Integer, x As Integer, y As Integer, z As Integer
z = 1
r = 1000
For x = 1 To 999
    For y = 1 To 9
        Cells(z, 1) = r
        r = r + 1
        Cells(z, 2) = Format(x, "000") & "-" & y
        z = z + 1
    Next
Next
End Sub

The number in Cells(z, 2) identifies column B. If you want to run for another column, change that number.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:40
Joined
May 7, 2009
Messages
19,169
for column1, just type any number on that column and on the next row,
put the formula:

=PreviousCell+1

example: Cell (A1), the value is 1000
next row: =A1+1

then copy this formula down the sheet.

for column2, create a Public function on a Module.
press Alt-F11 to go to VBA, type or paste the
below function:
Code:
Public Function fnIncrement(rng As Range) As String
    Dim var As Variant
    var = Split(rng.Value, "-")
    If Val(var(1)) = 9 Then
        var(0) = Val(var(0)) + 1
        var(1) = -1
    
    End If
    var(1) = Val(var(1)) + 1
    var(0) = Format(var(0), "000")
    fnIncrement = Join(var, "-")
    
End Function

now put the initial series in a Cell, eg. in Cell B1:

'001-1

on the next row below, put the formula:

=fnIncrement(B1)

copy this down your sheet.
 

JoseAcosta

New member
Local time
Today, 13:40
Joined
Aug 2, 2018
Messages
5
Hi the formula does the job the only thing is when it reaches 999-9 does not reset to 000-0.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:40
Joined
May 7, 2009
Messages
19,169
Add another code:
Code:
Public Function fnIncrement(rng As Range) As String
    Dim var As Variant
    var = Split(rng.Value, "-")
   If Val(var(0))=999 And Val(var(1))=9 then
      Var(0)=-1
   End If
    If Val(var(1)) = 9 Then
        var(0) = Val(var(0)) + 1
        var(1) = -1
    
    End If
    var(1) = Val(var(1)) + 1
   
    var(0) = Format(var(0), "000")
    fnIncrement = Join(var, "-")
    
End Function
 

JoseAcosta

New member
Local time
Today, 13:40
Joined
Aug 2, 2018
Messages
5
Hi, sorry to bother once with the formula but suddenly after an office update now shows #name? in all cells that had the =fnIncrement(B1)



I have try to check but all is the same and nothing had change but still showing that.


thanks!
 

Users who are viewing this thread

Top Bottom