Fill Specific Range of Cells with 3 Values Randomly to 3 Percentages

[jox51]

Greetings,

I have been trying to crack this for the last few days. So I have days of the month that need to be filled with specific number of people (say 3). Lets say their names are Bob, Tim, Jack. Out of the 30 days of the month, I need Bob to fill 50%, Tim to fill 30%, and Jack to take the remaining 20%. What I am trying to do is to fill these names in randomly throughout the month with the above specific percentages. These percentages will change each month and I was looking for a way to this via a formula where I can enter the percentages and the cells where their names will be listed with fill automatically. Thanks for your help.

I have attached a sample spreadsheet of what I am trying to accomplish.

V/R

Dom

[NickyC]

hi
one of the forum gurus can probably do this more elegantly, but this macro seems to work.

Please Login or Register  to view this content.

[jox51]

Awesome. Will take a look. Is there anyway to do this with a formula that doesn’t use the rand/randbetween or other volatile functions?

[jox51]

Tried the VBA above and I received a “Run time Error 91- object variable not set” error message

[NickyC]

oh dear - do you know which line is causing the error?

[jox51]

For each cc in DestRange.Cells

[NickyC]

hmm that should not be problematic =
can you try inserting

Please Login or Register  to view this content.

after

Please Login or Register  to view this content.

to see if it is recognising the range - it should return b6:b35

[jox51]

Got it working. I missed a line in the code. It works but only fills up the names up to the 15th of the month(b20)

[jox51]

Fixed, had to adjust the NumRange. This works perfectly. Thank you Nicky!