This is a very usefull, thank for sharing. But I would like to create a list as you have done, but instead of it choosing products from the list according to a random probability, I would like the products to be chosen according to their probability of occurrence. Could you help me with this? thanks in advance.
This is great stuff. I don't have deep exposure to VBA programming but your explanation and approach are pretty informative. thanks. I just want your opinion on whether we can use another way to avoid choosing an already selected name in the array, such as creating an array of random numbers against each of the names in the list from A2 to A16. then we can sort them and start populating the array by either ascending or descending order. For larger size arrays, I believe this may require less computational resources.
Hey this code was a great start for my project! I am building a cycle count location generator and this code allowed me to randomly select x number of locations to count. I have added code to input the date to the right of the location in the master list. is there a way to now avoid choosing this location again the next time i run the macro?
Thank you. With array formulas for sure. Here is a video of mine from back in 2015 using a formula to get a random name - ua-cam.com/video/Wrsnolpfej8/v-deo.html This can be improved in modern versions to get multiple names.
Is there a way to take out the how many option and instead have a list of cells covering an area from column B to M (eg B3, B12, C5, D7, E4, E9 etc) where, on the press of a button, those cells are infilled with the random names?
@@leonardoduarte7647 hi Leonard! My adaptation actually made it so that it went through both lists to choose names, which wasn’t helpful and caused repeats. Are you using this for a raffle? If so I can give you some tips and tricks I picked up. Sam
Yes absolutely Syr. The procedure would be the same as in the video. The only exception being that when we reference the names written in column A, we would have the reference the other sheet i.e. Worksheets("Sheet2").Range("A:A")
Hi. I know this comment is 3 yrs on lol..... but I found this and appreciate it. I was wondering how I can have the names removed from column A after it has been selected once. The reason being I'm trying to make it to be used for a competition drawing and need names that ave already been drawn removed. Is this possible?
Sure. Anything is possible with VBA :) Upon drawing we will need a small piece of code to either delete, or maybe cut and paste the name to another safe area for reuse next competition.
@@Computergaga Thanks for your reply mate. I was not sure you would seeing this is a clip from 3yrs ago. I a m not the best with VBA and am about to enroll in a course to help me with Excel and VBA but that wont start for 8 weeks. I know I may be asking too much but is there any chance you could help with that code mate? I have now subscribed to your channel as your vids are very good and very appreciated.
Thanks MADAussie. I have my own online Excel VBA course here - www.udemy.com/excel-vba-course/?couponCode=VBA1099 I just looked at the code I used in the video (as you say it was 3 years ago) and it checks if the name has already been picked. So we wouldn't need to remove a name. If we did we could scrap the check if the name has been picked part of the code.
Very helpful video! I've just started learning VBA and I'm trying to create a form that would randomize names. Is it possible to have the results shown in a textbox instead of a cell?
Hi, I have the same one subscriber problem we want results from two different columns, for example, A: A and B: B,, with two macros. The problem is that the second macro is also picking the names from column A: A and not from B: B, although I modified the range in the second macro to B: B! please answer.
Alan, Thank you this will be very helpful. couple questions can you set this macro up to preform numerous time within the same sheet? can you preset the number of names in the list you're extracting the names from? for example list 1 has 9 names out of 15 cells but the other cells are not blank (numbers etc), list 2 has 14 names and so on.
+Michael Hart All of which you ask can be done. Loops can be set to repeat within a sheet or loop through the sheets of a book. We can also set it to find the range rather than set column A. Message me if you want something specific set up.
Is there a way to set the range based on filtered results? Having the complete list is in column A, but based on another criteria in a separate column, when applying the filter, selecting from only the visible cells. For example, I have a list of staff and clients that participated in a fitness challenge, now I want to filter the list so that I can select 3 random winners that have completed 20 or more classes (filtered by the # of classes column) and that are clients (filtered by participant type column). In this instance, my total list changes from 111 to 66 people, but of course, the cells aren't 2 thru 67, they're still from 2 thru 112...
For sure. The loop at the end of the macro that prints the array to the worksheet would need editing. You would just need a way of coming up with these random cell ranges.
This is a very usefull, thank for sharing. But I would like to create a list as you have done, but instead of it choosing products from the list according to a random probability, I would like the products to be chosen according to their probability of occurrence. Could you help me with this? thanks in advance.
Hello, can you please tell me the mouse icon, how can I add it
This is great stuff. I don't have deep exposure to VBA programming but your explanation and approach are pretty informative. thanks. I just want your opinion on whether we can use another way to avoid choosing an already selected name in the array, such as creating an array of random numbers against each of the names in the list from A2 to A16. then we can sort them and start populating the array by either ascending or descending order. For larger size arrays, I believe this may require less computational resources.
Sure, sounds good.
This is very helpful to what I need. The only other thing I could really use is a way for it to pick a different group of numbers?
Great to hear, David. Different group of numbers? You can specify the number of names from the cell.
Hey this code was a great start for my project! I am building a cycle count location generator and this code allowed me to randomly select x number of locations to count. I have added code to input the date to the right of the location in the master list. is there a way to now avoid choosing this location again the next time i run the macro?
This is really nice. I was wondering tho, if it's possible to do this without VBA...using helper columns & array formulas?
Thank you. With array formulas for sure. Here is a video of mine from back in 2015 using a formula to get a random name - ua-cam.com/video/Wrsnolpfej8/v-deo.html
This can be improved in modern versions to get multiple names.
Is there a way to take out the how many option and instead have a list of cells covering an area from column B to M (eg B3, B12, C5, D7, E4, E9 etc) where, on the press of a button, those cells are infilled with the random names?
I guess so, but I don't have the code for that.
Hi great one really needed that ,can you help me I need more then one result , like you have in d6 I would need 6 more o try but lost
also if i have more than one sheet in the workbook, do i have to specify "counta(range(!Sheet4.A:A))"
How can I delete the row that the name has been chosen from? Is this possible in this sequence?
Thanks!
Nevermind! I just adapted it to check the row that I already have the chosen names on. Thank you!!!
@@samanthascott6933 Hi trying, do the same, but I am a newbie in VBA could you please share your modified code with me?
@@leonardoduarte7647 hi Leonard! My adaptation actually made it so that it went through both lists to choose names, which wasn’t helpful and caused repeats.
Are you using this for a raffle? If so I can give you some tips and tricks I picked up.
Sam
I dont see an "A" icon at the top portion of my sheet. What is the command formula for the VBA to be useful?
I have try to copy your code, but there is a problem of "ReDim Names(1 To HowMany) 'Set the array size to how many "
I do not know why, how you help?
I understand now, thank you!
@@codymak3678 I'm having the same issue. What was your solution?
Never mind. Solved it :)
Great info. Is there a way to retrieve the data from another sheet?
Yes absolutely Syr. The procedure would be the same as in the video. The only exception being that when we reference the names written in column A, we would have the reference the other sheet i.e. Worksheets("Sheet2").Range("A:A")
Thanks!! this worked like a charm
Computergaga I typed “NoOfNames=Application.CountA Worksheets (“Sheet2”).Range (“A:A”)-1” but the syntax is wrong please help? Thank you.
Thanks for sharing! A little modification and I could have a VBA lottery!
Thank you.
Hi. I know this comment is 3 yrs on lol..... but I found this and appreciate it. I was wondering how I can have the names removed from column A after it has been selected once. The reason being I'm trying to make it to be used for a competition drawing and need names that ave already been drawn removed. Is this possible?
Sure. Anything is possible with VBA :)
Upon drawing we will need a small piece of code to either delete, or maybe cut and paste the name to another safe area for reuse next competition.
@@Computergaga Thanks for your reply mate. I was not sure you would seeing this is a clip from 3yrs ago. I a m not the best with VBA and am about to enroll in a course to help me with Excel and VBA but that wont start for 8 weeks. I know I may be asking too much but is there any chance you could help with that code mate? I have now subscribed to your channel as your vids are very good and very appreciated.
Thanks MADAussie. I have my own online Excel VBA course here - www.udemy.com/excel-vba-course/?couponCode=VBA1099
I just looked at the code I used in the video (as you say it was 3 years ago) and it checks if the name has already been picked. So we wouldn't need to remove a name. If we did we could scrap the check if the name has been picked part of the code.
Very helpful video! I've just started learning VBA and I'm trying to create a form that would randomize names. Is it possible to have the results shown in a textbox instead of a cell?
For sure. Though I don't have the code to do this to hand.
Thank you Sir,
You're very welcome, Shruthi.
Sir how to connect all columns on this please help on this
Hi, I have the same one subscriber problem we want results from two different columns, for example, A: A and B: B,, with two macros. The problem is that the second macro is also picking the names from column A: A and not from B: B, although I modified the range in the second macro to B: B! please answer.
Where the Cells object is used in the code, it references column 1 aka A:A. This needs to be changed to 2.
thank you a lot for replaying .the problem fixed
Excellent!
Alan, Thank you this will be very helpful.
couple questions
can you set this macro up to preform numerous time within the same sheet?
can you preset the number of names in the list you're extracting the names from? for example list 1 has 9 names out of 15 cells but the other cells are not blank (numbers etc), list 2 has 14 names and so on.
+Michael Hart All of which you ask can be done. Loops can be set to repeat within a sheet or loop through the sheets of a book. We can also set it to find the range rather than set column A.
Message me if you want something specific set up.
Is there a way to set the range based on filtered results? Having the complete list is in column A, but based on another criteria in a separate column, when applying the filter, selecting from only the visible cells. For example, I have a list of staff and clients that participated in a fitness challenge, now I want to filter the list so that I can select 3 random winners that have completed 20 or more classes (filtered by the # of classes column) and that are clients (filtered by participant type column). In this instance, my total list changes from 111 to 66 people, but of course, the cells aren't 2 thru 67, they're still from 2 thru 112...
Is there a way to make the results appear on different cells?
i.e. A1, B4, H3
For sure. The loop at the end of the macro that prints the array to the worksheet would need editing. You would just need a way of coming up with these random cell ranges.
How can I setup required number against all listed name and all name picked by automatically
Sorry, I don't understand your question.
can this be used with numbers
Absolutely
I can't make that A button at the top to show.
ua-cam.com/video/VVpTP9W39R0/v-deo.html