I'm looking to create a 5-digit random but unique alphanumeric code in excel. I'm trying to keep the format of letter, number, letter, number, letter. Examples: d5s3e, h2x0f, j5n9n, d0a6z, y8c1p, q6z3b. I'm trying to stay away from the RANDBETWEEN function but am not sure how to craft a macro or something in the VBA that might get me what I want. The biggest problems is creating the random codes but not creating any duplicates. Any help would be awesome!
Here you go. You didn't specify where you wanted the output, nor how many codes you want, so this code generates 1 code and puts it in a messagebox.
As for not creating duplicates, this code doesn't address this for the same omissions mentioned above. However, in generating just 2 codes, there is a ~1 in 1.75 million chance that the codes would be the same, so really I'm not sure that duplicate checking is really necessary unless you're generating a massive amount of codes.
However, if you do specify how you want the output, checking for duplicates would be fairly easy with .Find
Please Login or Register to view this content.
Last edited by walruseggman; 07-15-2015 at 01:58 PM.
Hello walruseggman,
An easy method of avoiding duplicates is to add each code to a Collection. The collection object will generate a trappable error if the code already exists. The collection items can then be retrieved later.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
@ Leith Ross: good idea, a dictionary would work well, I suppose to you could also use an array and then Ubound(Filter()) to check the array for existing code.
I'm guessing a dictionary would be the fastest method though between dictionary, array, and .Find
DUPLICATE:
http://www.excelforum.com/excel-prog...eric-code.html
OP - please don't duplicate questions.
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
Hi mcodden,
The following Macro should help you get started.
LewisPlease Login or Register to view this content.
This is a duplicate thread. See http://www.excelforum.com/excel-form...ml#post4132254
Last edited by LJMetzger; 07-15-2015 at 02:26 PM.
Lewis,
That Macro worked perfectly! Thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks