Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-02-2018, 11:14 AM   #1
JoseAcosta
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
JoseAcosta is on a distinguished road
Post Continues number issue

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

JoseAcosta is offline   Reply With Quote
Old 08-02-2018, 11:44 AM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 498
Thanks: 0
Thanked 113 Times in 113 Posts
June7 will become famous soon enough
Re: Continues number issue

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
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
JoseAcosta (08-02-2018)
Old 08-02-2018, 11:58 AM   #3
JoseAcosta
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
JoseAcosta is on a distinguished road
Re: Continues number issue

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?

JoseAcosta is offline   Reply With Quote
Old 08-02-2018, 12:28 PM   #4
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 498
Thanks: 0
Thanked 113 Times in 113 Posts
June7 will become famous soon enough
Re: Continues number issue

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.
June7 is offline   Reply With Quote
Old 08-02-2018, 07:10 PM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,353
Thanks: 54
Thanked 2,030 Times in 1,943 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Continues number issue

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-08-2018, 10:57 AM   #6
JoseAcosta
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
JoseAcosta is on a distinguished road
Re: Continues number issue

Hi the formula does the job the only thing is when it reaches 999-9 does not reset to 000-0.
JoseAcosta is offline   Reply With Quote
Old 08-08-2018, 11:14 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,353
Thanks: 54
Thanked 2,030 Times in 1,943 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Continues number issue

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-09-2018, 05:09 AM   #8
JoseAcosta
Newly Registered User
 
Join Date: Aug 2018
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
JoseAcosta is on a distinguished road
Re: Continues number issue

Thanks! Works Perfect...

JoseAcosta is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
help with continues form qupe Forms 11 02-15-2017 08:44 AM
Selection from continues form Dezirous Forms 3 08-27-2009 11:33 PM
Table continues to corrupt thart21 Tables 3 11-13-2005 07:04 PM
Access Continues Forms Pleasure Forms 5 07-27-2005 12:12 PM
The Saga Continues... samonwalkabout General 1 04-30-2003 06:38 AM




All times are GMT -8. The time now is 09:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World