Generate random alphanumeric code

[mikeha_99]

Hello,
I would like to generate a random 5 character code. The random letters should be caps, and numbers between 0-9. An example might be: "F7B4K"

Ideally, at least one character in the result would be a letter.

I look forward to any and all suggestions.

Thank you,
Mike

 

[haffy311]

Is there a way to make the resulting value in the cell remain the same? I dont want the cell to change when other cells are updated or the sheet is refreshed.

=CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))

Via formula, you could try:

=CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))

to get a random alphanumeric character... or, 5 of those concatenated to get a 5 character one: =CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))

Just press F9 to get a new value, or use a looping macro for your "... at least one character in the result would be a letter" restriction.

note: RANDBETWEEN requires the analysis toolpak.

 

[Wessie]

Hi Haffy

You can switch off automatic calculations.

Open the Tools menu and click Options.
In the Options dialog box, click the Calculations tab.
Then, click on the radio button labeled Manual, and click OK to close the dialog box.
If you want to check a calculation while you are working, simply press F9.

The other option is copying and pasting for values to keep it the same.

Let me know if that was not sufficient.

Wes

Is there a way to make the resulting value in the cell remain the same? I dont want the cell to change when other cells are updated or the sheet is refreshed.

=CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))

 

[haffy311]

Thanks Wessie. What about trying to exclude a couple of letters from the list 'I' (73) and 'O' (79). I tried to exclude them from the formula, but now it won't generate above H (72).

=CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,72),RANDBETWEEN(74,78),RANDBETWEEN(80,90)))

Hi Haffy

You can switch off automatic calculations.

Open the Tools menu and click Options.
In the Options dialog box, click the Calculations tab.
Then, click on the radio button labeled Manual, and click OK to close the dialog box.
If you want to check a calculation while you are working, simply press F9.

The other option is copying and pasting for values to keep it the same.

Let me know if that was not sufficient.

Wes

 

[Wessie]

What exactly are you trying to achieve?
Maybe if I understand that part I can help out more.

Thanks Wessie. What about trying to exclude a couple of letters from the list 'I' (73) and 'O' (79). I tried to exclude them from the formula, but now it won't generate above H (72).

=CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,72),RANDBETWEEN(74,78),RANDBETWEEN(80,90)))

 

[haffy311]

Thanks Wessie. I just removed the I and O manually via autofilter. Thanks!

 

[shg]

Or =MID("ABCDEFGHJKLMNPQRSTUVWXYZ", RANDBETWEEN(1,24), 1)

 

[fredetchic]

Hi!

This code is really awsome and does the job... but

I don't know if it's only me (VBA newbie) but this creates the same string for each cell, even though there's the randomize function

Sub createPW()
Dim pw As String
Dim i As Integer
Randomize
For i = 1 To 5
    If Int((2 * Rnd) + 1) = 1 Then
        pw = pw & Chr(Int(26 * Rnd + 65))
    Else
        pw = pw & Int(10 * Rnd)
    End If
Next i
ActiveSheet.Range("C1:C200").Formula = pw
End Sub

I'd like the script to create a unique code for each cell between C1 to C200

I've read another message in this thread that say to go to Tools/Option about calculation but I have a french version and does not see any option that talk about calculation

Moreover, in my situation, I want each string to be different (as the other member wanted it to be the same for each cell).

I'm struggling finding this answer over the net and it drives me crazy

I tryied every option of copy/paste but still get no luck...

Fred

 

[Scott Huish]

That's because your assigning the same value to each cell. In order to generate different values you need to loop through the code that generates the random string. However I don't think the requirements of the initial question are what you are after. What kind of code are you looking to generate.

 

[fredetchic]

That's because your assigning the same value to each cell. In order to generate different values you need to loop through the code that generates the random string. However I don't think the requirements of the initial question are what you are after. What kind of code are you looking to generate.

Yep, I understand that this line code

[COLOR=#574123]ActiveSheet.Range("C1:C200").Formula = pw[/COLOR]

Do assign the same value to each cell between C1 to C200! (at first I though it was a kind of loop but I deduce it was not when I saw the result!)

In fact, what i'd like to do is the following:

I want a random 5 or 6 alphanumeric string like the one generated by this code (if I want 6, just have to replace "For i = 1 To 5" by "For i = 1 To 6"
But I want that each cell beetween C1 and C200 has a different string.

Ex:
C1 = AG56T4
C2 = G45TR4
...
C200 = P55P5O

I really don't care if it has 3 numbers and 3 letters or 4 numbers and 2 letters, or whatever combinaison of letter and numbers
as long as all strings are unique.

I tryied to check on the net on how to implement a loop but everytime, it fails! I'm not familiar enough with VBA

I'm more "monkey this" than really write VBA code. In other word, I'm "tweaking code" more than "writting code"!


So, how could I add to this code the following steps:

State range where this macro applies (like from C1 to C200)
Create a loop from C1 to C200
For each Cell between C1 and C200, generates an unique string like the one in existing code
write this string in the cell


I'm sure it's a dumb*** thing written in 5 lines but cannot figure out how achieve that

Thanks for the input!

Fred

 

[Scott Huish]

Sub UniqueCodes()
Dim LengthOfCode As Long, strCodeChars As String, uniquecode As String
Dim x As Long, r As Long
LengthOfCode = 6
strCodeChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
With CreateObject("scripting.dictionary")
For r = 1 To 200
    Do
            For x = 1 To LengthOfCode
                uniquecode = uniquecode & Mid(strCodeChars, Application.RandBetween(1, 36), 1)
            Next
            If Not (.exists(uniquecode)) Then .Add uniquecode, 1: Cells(r, 3) = uniquecode: uniquecode = "": Exit Do
            
    Loop
Next
End With
End Sub