(Solved) – Unique Id Generator and Randomizer VBA

  • by

I am trying to a few items for a Secret Santa spreadsheet.

  1. A Unique ID generator to print the UID in Column B for a list of names in Column A and

  2. A randomizer to print the ID numbers in a random order in Column C with the restraint that Column B UID cannot equal Column C UID, ensuring no one “gets themselves in the Secret Santa.”

  3. List Name for the random UID in Column C in Column D.

The UID’s are to start at 1 and then count until the last name receives an ID. I also want the generator to be able to handle creating an ID for a name that is added anywhere within the list (beginning, middle, end).

I have done some research, but found some quite complicated answers here and on other websites. Some use complicated looping others the GUID function that I am not quite sure I understand. In general, the answers are for existing lists and not a new list with no UID’s.

I am a beginner at coding/software architecture so I assume that I would want to:

1) Create the UID’s and print them to Column B.
2) Save Column A and B into an array??
3) Randomize and Print the UID’s into Column C.
4) Use the array to get the name for the randomized UID’s in Column C and print the corresponding name in Column D.

I am unsure if this methodology is a “good” approach for this type of problem, but I would be interested in hearing any other methodologies as well. If any one could help me with the code it would be greatly appreciated. The only thing I have so far is the row counter which is below.

Sub secret_santa()

Dim person_count As Integer
Dim uid As Integer

'Count Number of Used Rows
person_count = ActiveSheet.UsedRange.Rows.Count

'Subtract Header from person_count
person_count = person_count - 1

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *