Help Urgent! Please help me. Thank You!

[kopitam]

Hi,
Does anyone how to generate a sequential number. the number generated must be 6 digits.
Example 100000 follow by 100 001. I need the code
badly. Thank You

[Quintonir]

Just for clear understandings, you want to start with the number 100000 and end with 999999? And what do you want to do with it?


Quintonir

[HeSaidJoe]

'you now have an array holding the generated
'numbers 100001 to 150001

Private Sub Command3_Click()
    Dim x As Long
    x = 100000
    Dim y(100000 To 150000)
    
    For x = x To 150000
     y(x) = x
     List1.AddItem y(x)
    Next x
End Sub

[kopitam]

I need that for adding to the file name. For instance,
the file name is WATER. Then after generating the number The new fileName is WATER121111. Thank You for you help Quintonir and HeSaidJoe !

[HeSaidJoe]

'here is something to play with.
'I put the numbers first as it makes less steps to extract the number
'form the name

'I didn't test this but it should work

'you will need to do some err handling on this and I assume it works
'as I used it in some prior app and just flipped it a little for your use
'you can adjust the number of digits to reflect your needs.

'increment a file based on last numberfile in folder
'needs to be passed a directory..ie C:\myfolder
Option Explicit

'Assuming you have a folder called myFolder and in it you have files
'with the last 3 digits being numbers

Public Function GetNum(sDir As String) As Integer
    On Error GoTo errHandler:
    
    'Access all files within a folder
    
    Dim stFile As String
    Dim myArr()
    Dim i As Integer
    
    stFile = Dir$(sDir & "*.*")
    If stFile = "" Then
        GetNum = Format(1, "000")  'based on 3 digits  1 to 999
    Exit Function
    End If
    
    Do While stFile <> ""
    'if you want to access each file you must use the dir
    'and the file
    'assuming a file is Water000 to Water999 resides in the dir
    ReDim Preserve myArr(i)
    myArr(i) = Left(stFile, 3)
    myArr(i) = CInt(myArr(i))
    stFile = Dir
    Loop
    Call SortMe(myArr)
    GetNum = Format(myArr(UBound(myArr)), "000")
    GetNum = GetNum + 1
    GetNum = Format(GetNum, "000")
    If GetNum >= 900 Then
        MsgBox "Please delete old files as increments only go to 999", _
            vbCritical, "File Number Problem Building"
    End If
    
    Exit Function
    
errHandler:      'should not reach here except by error and that error
                'will more than likely be a file error
     errSub
 Exit Function
 Unload Me
End Function

'sort an array by number
Public Sub SortMe(iArray As Variant)

Dim lLoop1 As Long
Dim lLoop2 As Long
Dim lTemp As Long

For lLoop1 = UBound(iArray) To LBound(iArray) Step -1
For lLoop2 = LBound(iArray) + 1 To lLoop1

If iArray(lLoop2 - 1) > iArray(lLoop2) Then
lTemp = iArray(lLoop2 - 1)
iArray(lLoop2 - 1) = iArray(lLoop2)
iArray(lLoop2) = lTemp
End If
Next lLoop2
Next lLoop1
End Sub

Private Sub Command1_Click()
  Dim x As Integer
  x = GetNum("C:\myFolder")  'the last number used in the folder
  sfilename = "C:\myFolder\" & x & "water.txt"  'my new filename
  MsgBox sfilename
End Sub