Hi – in another thread “Dick_C” was looking for a macro to assign guests to a progressive dinner. I was trying to come up with a solution that would let him enter the number of guests and hosts, press a button, and then generate a list of guests for each host, with each guest identified by number.
I was doing this by generating a random number for each guest, saving the number in VBA array, then determing the rank of that random number against all other random numbers (and also saving that rank in an array) – the first “X” guests would go to ‘host 1,’ the next “X” to ‘host 2’ and so on.
I came up against two problems, and I wonder if anyone has any suggestions.
First, there seems to be no way to refer to the array contents directly. What I really wanted was something like:
Guests(i, 1) = Application.WorksheetFunction.Rank(RandomArray(i), RandomArray, 1)
which is analogous to the way that the RANK function operates on the worksheet. Perhaps I was just missing the syntax to do this directly, but in order to reference the results of RandomArray, I had to write it to a range on the s/sheet – then VBA was quite content with:
Guests(i, 1) = Application.WorksheetFunction.Rank(RandomArray(i), [RandomRange], 1)
is it possible to refer to the entire array as an entity (ie as a collection? or something??), so that it doesn’t have to be pasted onto the sheet? Pasting it on the sheet looks sloppy, and requires that the array have fewer than 257 members, since I was pasting it in a row and there are only 256 columns for the data to go into.
After some more research (thank you, John Walkenbach) I was able to figure out how to fill the worksheet range vertically (you have to use ‘application.worksheetfunction.transpose(RandomArray),’ in case anyone is interested!) but it still imposes a limit of 5,461 entries in the array – it seems that more entries than that cause the “transpose” function to crash.
Admittedly, this is more a theoretical than a practical limitation for organizing a social event, but now I am curious. I have attached the file in its current incarnation, which is slightly different than the last one I posted for Dick_C due to the vertical orientation of the random number ranges on the s/sheet.
