Auto-Generate a 6-digit number

imimin

Active Member

Joined

May 9, 2006

Messages

404

• Jul 20, 2006

• #2

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

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

74,325

Office Version

1. 365

Platform

1. Windows

• Jul 20, 2006

• #3

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"),"")

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Jul 20, 2006

• #4

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

 

Upvote 0

imimin

Active Member

Joined

May 9, 2006

Messages

404

• Jul 20, 2006

• #5

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

 

Upvote 0

imimin

Active Member

Joined

May 9, 2006

Messages

404

• Jul 20, 2006

• #6

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

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

74,325

Office Version

1. 365

Platform

1. Windows

• Jul 20, 2006

• #7

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)

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Jul 20, 2006

• #8

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

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

Code:

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.

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,650

Office Version

1. 365

Platform

1. Windows

• Jul 20, 2006

• #9

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

Code:

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)

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Jul 20, 2006

• #10

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...

 

Upvote 0