Random value generation?

[Delta223]

Hi all,



Anyone know if it is possible to generate random values using a formula in Excel?

My ideal values would be: Alpha Alpha Numeral Numeral Numeral (ex KF938, MC823, etc) This gives me all the easy to remember SKU #s I want.

If random alphas aren't possible I can also use 4 or 5 digit numeral values (ie 8329, 27463)

Thanks!

[apr pillai]

1. Press ALT+F11 to display the VBA Window.
2. Insert a Standard Module.
3. Copy and paste the following Code into the module and save it.

4. Public Function CustRnd() As String
   Dim strAlpha As String, num As Integer
   Dim strout As String, j As Integer
   
   Randomize (Timer)
   
   strout = ""
   For j = 1 To 2
     num = Int(Rnd(1) * 26) + 1
     strout = strout & Chr$(64 + num)
   Next
   
   j = Int(Rnd(1) * 999) + 1
   strout = strout & Format(j, "000")
   CustRnd = strout
   
   End Function

5. Press ALT+F11 again to go back to the worksheet.
6. Enter the Expression =CustRnd() in any cell where you want a Random Number in XXnnn format, where XX represents alphabets and rest numerals.

OR


Highlight a rectangular Range, Type =CustRnd(), press and hold the Ctrl Key then press Enter Key.


You may use the CustRnd() function combined with other expressions also.

[Delta223]

Thanks so much! I'll try it now

[Delta223]

Hi,

I decided to go with one less numeral to make it less confusing (HF32, UC04, XL87, etc)

I tried to change the VBA code but could not get it to work. How can I reduce the code to create Alpha Alpha Numeral Numeral?

Thanks again!

[apr pillai]

Change the following lines in the code

j = Int(Rnd(1) * 999) + 1
strout = strout & Format(j, "000")

To

j = Int(Rnd(1) * 99) + 1
strout = strout & Format(j, "00")

i.e. 999 to 99 and Format(j,"000") to Format(j,"00")