Random Code Generator Macro

[PayneA]

Hi, new to the forum and hoping someone can help

I'm very new to using macros, still picking up the basics so forgive me if some parts aren't clear or the answer seems very obvious.

I am trying to create a spreadsheet that can create random code, for use on gift vouchers. I was going to create the codes using a combination of formula which would include: "=CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)" repeated over as many times as needed. The problem I found with this is that the sequence of numbers and characters would always be the same, where I want it to be completely random and in no pattern.

After searching the internet, I have found this code which is the closest I have found to what I am looking for so far:

Public Function RandomizeF(Num1 As Integer, Num2 As Integer)

Dim Rand As String
Application.Volatile
getLen = Int((Num2 + 1 - Num1) * Rnd + Num1)
Do
i = i + 1
Randomize
Rand = Rand & Chr(Int((85) * Rnd + 38))
Loop Until i = getLen
RandomizeF = Rand
End Function

Using this code, when I enter the formula =RandomizeF(9,10) it randomly generates a code 9-10 characters long, and in no particular sequence.

The problem I have is that this code also includes special characters, where I only want the code generated to contain A-Z, 0-9. I have tried altering the code and tried to find a way to do it without special characters but have had no luck.

Does anyone know how I can achieve random code without special characters, either by editing my existing code, or another method for doing this?

thanks in advance

 

[dgyang]

You can see from the ASCII table below, character 0 refers to 48, and character z refers to 122. However, there are some special characters within the range.

Therefore, you need to check the random number returned and ensure the number is not referring to a special character.

[B]Public Function RandomizeF(Num1 As Integer, Num2 As Integer)
[B]Dim Rand As String, RandTemp As String
[B]Application.Volatile
[B]getLen = Int((Num2 + 1 - Num1) * Rnd + Num1)
[B]Do
[B]i = i + 1
Do
[B]Randomize
[B]RandTemp =Int((84) * Rnd + 48))
Loop Until (RandTemp <= 57) OR (RandTemp >=65 AND RandTemp <= 90) OR (RandTemp >=97)
Rand = Rand & Char(RandTemp)
[B]Loop Until i = getLen
[B]RandomizeF = Rand
[B]End Function[/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B]

 

[Michael M]

Just as an aside, you don't need code to do this, you could simply place the numbers 1 to 9, followed by A to Z...down column "A"
A1 to A36
then use, for an 8 digit alphanumeric code number

=INDEX($A$1:$A$36,RANDBETWEEN(1,36))&INDEX($A$1:$A$36,RANDBETWEEN(1,36))&INDEX($A$1:$A$36,RANDBETWEEN(1,36))&INDEX($A$1:$A$36,RANDBETWEEN(1,36))&INDEX($A$1:$A$36,RANDBETWEEN(1,36))&INDEX($A$1:$A$36,RANDBETWEEN(1,36))&INDEX($A$1:$A$36,RANDBETWEEN(1,36))&INDEX($A$1:$A$36,RANDBETWEEN(1,36))

 

[PayneA]

knew there'd probably have been an easier way than using macros, that formulas done exactly what I needed, thanks for your help!

 

[Michael M]

Glad it worked...