Macro to find Name, Select (random) corresponding value

[jnes]

Hello,

i've been tasked with creating a macro to determine if a name is present in a list, and if it is present - randomly select a value(case) corresponding to that person from another column. So for example, in the sample data i pasted below, how to find if Will is present, then randomly select one of his cases? any assistance would be much appreciated


Cases EmpIDs
CA70914 Will
CA11254 Will
CA75853 Jill
CA69664 Jim
CA87459 Jim
CA72493 Jess
CA73302 Mike
CA74037 Bill
CA74791 Will
CA78075 Grant
CA74436 Karen
CA75873 Alice
CA75266 Nora

[JBeaucaire]

This will give you the base code to play with, you will need to work out the logistics of where the data is coming from and the input of the name "Will". But this shows you a technique.

Please Login or Register  to view this content.

[jnes]

Thanks JB really helpful! is there a way to have this start with an input box so the user can input a name to search?

[JBeaucaire]

Like so, edits in red:

Please Login or Register  to view this content.

[Akuini]

Just another way:

Please Login or Register  to view this content.

Note:
If you have a lot of unique names, say tens or hundreds, on the list then I suggest you use a combobox, so you can choose a name instead of typing. Typing names is prone to errors.

[jnes]

thank JB, this is amazing! If I had a date column, how would I get that to appear in the MSG box as well? For example; the Msgbox returns Random Case CA12345, date 12/5/2019 .... thank you so much

[alansidman]

Closed. This request has been opened in Commercial Services