Randomize teams button

[Retroshift]

Hi, I have the following code to randomize couples with a button.

However, there are two issues with this VBA code:
1st issue: You have to manually select the range before the button macro can be applied. Is there a way to automatically selecting the range(s) of the teams in the code?
2nd issue: Is it possible to randomize in such a way that e.g. Team I & Q get randomized together in the second range, let's say on fixed position 16 instead of only on the positions 1-10?

I've attached the file. Thanks

Please Login or Register  to view this content.

[Richard Buttrey]

Can you explain from first principles what you want to achieve and manually add a couple of end results.

It may be possible to do this with functions rather than macros. I find it difficult to understand your end goal from the explanation you've given so far.

[Retroshift]

Sure. So, I have 20 couples for a tournament. The columns are positioned as seen in the attached image in order to fit on a printed page.
The names of team member 1 and team member 2 have to be manually entered (on the image they are just letters).

My goal is twofold:
1) Shuffle merged cells BC2:BC11 (team member 1) as a pair with merged cells EF2:EF11 (team member 2) AND
merged cells IJ2:IJ11 (team member 1) as a pair with merged cells LM2:LM11 (team member 2) randomly throughout the fixed positions 1-20.
For example team I-Q (now on fixed position 1) gets randomly placed next to fixed position e.g. 18 or 9 or 11 or 4 ... The same goes for the other teams.
This with a click on the randomize button.

2) Before the randomize button is able to operate the macro, you first have to manually select the ranges. However, it would be a lot more user friendly if
the button code already has the above mentioned ranges of team members selected so you only have to click the button without first having to manually select the ranges.

I hope this is more clear. Thanks!

[Richard Buttrey]

Hi,

Are the merged cells of any importance? We generally advise against using merged cells since they cause too many problems?

I'm still unclear as to what you expect to happen. Just highlighting cells A2:A11 as the note says simply randomises A2:A11 it doesn't affect any other columns. I'm assuming that you first select A2:E11, but even then only columns A&B change.

Could you upload the workbook with a second sheet that shows what should have happened after randomising?

[jindon]

Merged cells are not good at all.

Try this one and see if this is how you wanted.

Please Login or Register  to view this content.

[Retroshift]

I need the merged cells for printing purposes. As attached you can find a possible result of shuffling team entries on Excel Sheet2.

I used colours to show that the team members should stay together when you hit the shuffle button. The fixed positions should stay in the same cell but not the teams assigned to those fixed positions (1 to 20).

How can the VBA code on the shuffle button be altered so you do not have to highlight/select these ranges beforehand? The button should already know what the ranges to shuffle are.

[jindon]

How it is not working?

[Retroshift]

Thanks, Jindon, this works very well. However, I do not understand the code that well to change the array.

I applied this in another file (see attached) but it didn't work. Could you point out how to change the array?

[jindon]

Try

Please Login or Register  to view this content.

Edit: Found a bug so code has been fixed.

[Retroshift]

Hi Jindon, this does not work when you click several times on the button. In the end you get the same names in the entry cells.

Also, when I write text in the row above the array, there are cells in that row that pop up entry names after hitting the button (thus outside of the range of entries).

[jindon]

Yep, I have just fixed a code, so copy and try again.

[jindon]

Also, when I write text in the row above the array, there are cells in that row that pop up entry names after hitting the button (thus outside of the range of entries).

Then change to

Please Login or Register  to view this content.

[Retroshift]

Thanks, Jindon. The code runs smoothly. You are a VBA wizzard!

[jindon]

You are welcome and thanks for the rep.