Auto-Generate a 6-digit number

[imimin]

What fuction can I use in a cell to "auto generate" a number. That is, for each new row created, I would like one of my columns to "auto generate" a 6-digit number (in sequence).

 

[imimin]

I need to set this up in the formula bar for a specific column

 

[Joe4]

Does it need to be random? If not, you can make use of the ROW() function, i.e.

The following will return the row number in 000000 format:
=TEXT(ROW(),"000000")

The following will do the same, but only show a value if cell A1 is populated:
=IF(A1<>"",TEXT(ROW(),"000000"),"")

 

[Smitty]

How are you adding a new row?

In sequence, couldn't you just start with a baseline 6-digit number and use =A1+1?

Smitty

 

[imimin]

Jm14-that works great, but how could I set a beginning number (such as 278349)?

 

[imimin]

pennysaver-In this case, I would like to NOT base the calculation on another cell.

 

[Joe4]

It depends on which row you are starting in. If you are starting on row 2, try:

=TEXT(ROW()+278347,"000000")
and copy the formula down for all rows (or use the same logic in the other version I provided)

 

[NateO]

I guess this isn't what you want...

But random without leading zeros requires a bit of trickery, it seems... E.g.,

Public Function rndLong(ByVal lngLength As Long) As Long
Dim b() As Byte, keyArr1() As Byte, keyArr2() As Byte
Dim i As Long, tmpStr As String
Let keyArr1 = "0123456789"
Let keyArr2 = "123456789"
ReDim b(1 To lngLength * 2)
Let b(1) = keyArr2(Int(((UBound(keyArr2) + 1) \ 2) * Rnd + 1) * 2 - 2)
For i = 3 To lngLength * 2 Step 2
    Let b(i) = keyArr1(Int(((UBound(keyArr1) + 1) \ 2) * Rnd + 1) * 2 - 2)
Next
Let tmpStr = b
Let rndLong = tmpStr
End Function

Sub foo()
Dim i As Long
For i = 1 To 100
    Debug.Print rndLong(6)
Next
End Sub

You could use RandBetween(), too, this requires the Analysis Tookpak to be loaded.

 

[Scott Huish]

Probably getting off course here, but Nate, I think you're function could possibly be written like this:

Public Function rndlong(ByVal lngLength As Long) As Long
Dim ub As Long, lb As Long
Randomize
lb = 10 ^ (lngLength - 1)
ub = (10 ^ lngLength) - 1
rndlong = Int((ub - lb + 1) * Rnd + lb)
End Function

For specifically a 6 digit random number without leading 0 as a worksheet function, perhaps:

=INT(899998*RAND()+100000)

 

[NateO]

Yes, that looks like it could work and should be more efficient.

My function was an adaptation from a broader question, where keyArr could house anything, e.g., Alpha-Numeric chars...