Does anyone have a VBA code that will randomly sort a set of 'Selected' cells
in a column when a forms object assigned to that code is selected?

Thanks,
Guy

Guy wrote...
Does anyone have a VBA code that will randomly sort a set of 'Selected' cells
in a column when a forms object assigned to that code is selected?

You mean shuffle the cell values in a range? Maybe something like


Sub foo()
Const FOOBAR As Long = 4

Dim v As Variant, t As Variant
Dim i1 As Long, i2 As Long, j1 As Long, j2 As Long
Dim k As Long, m As Long, n As Long

If Not TypeOf Selection Is Range Then Exit Sub
v = Selection.Areas(1).Value2
k = Selection.Areas(1).Rows.Count
m = Selection.Areas(1).Columns.Count
n = FOOBAR * k * m

Do While n 0
n = n - 1
i1 = Int(1 + k * Rnd)
i2 = Int(1 + k * Rnd)
j1 = Int(1 + m * Rnd)
j2 = Int(1 + m * Rnd)
If i1 < i2 And j1 < j2 Then
t = v(i1, j1)
v(i1, j1) = v(i2, j2)
v(i2, j2) = t
End If
Loop

Selection.Areas(1).Value2 = v
End Sub

That doesn't seem to do anything. I am just a little better than a novice at
VBA code, if the code needs to be modified for my application, I can't figure
out how.
I have a range of cells in column E that is selected, not always the same
number of cells are selected, and I need to randomly sort the selected cells
when I click on a forms button put on the sheet.

Thanks,
Guy

"Harlan Grove" wrote:

Guy wrote...
Does anyone have a VBA code that will randomly sort a set of 'Selected' cells
in a column when a forms object assigned to that code is selected?

You mean shuffle the cell values in a range? Maybe something like


Sub foo()
Const FOOBAR As Long = 4

Dim v As Variant, t As Variant
Dim i1 As Long, i2 As Long, j1 As Long, j2 As Long
Dim k As Long, m As Long, n As Long

If Not TypeOf Selection Is Range Then Exit Sub
v = Selection.Areas(1).Value2
k = Selection.Areas(1).Rows.Count
m = Selection.Areas(1).Columns.Count
n = FOOBAR * k * m

Do While n 0
n = n - 1
i1 = Int(1 + k * Rnd)
i2 = Int(1 + k * Rnd)
j1 = Int(1 + m * Rnd)
j2 = Int(1 + m * Rnd)
If i1 < i2 And j1 < j2 Then
t = v(i1, j1)
v(i1, j1) = v(i2, j2)
v(i2, j2) = t
End If
Loop

Selection.Areas(1).Value2 = v
End Sub

Guy wrote...
That doesn't seem to do anything. I am just a little better than a novice at
VBA code, if the code needs to be modified for my application, I can't figure
out how.
....

Begging the question why you're trying to do this.

You'd need to put the code in my previous response into a GENERAL VBA
module. For example, press [Alt]+[F11] to open the Visual Basic Editor
(VBE), then run the VBE menu command Insert Module. That should open
a window showing a new, empty general module. Paste my code into it.

Then press [Alt]+[F11] again to switch back to Excel. Right-click on
the form control you want to use to shuffle the selected cells'
values, and assign the macro foo to it.

I did that, it doesn't seem to be doing anything.
The column has names of people playing in a tournament. I need to randomly
sort the cells for bracket assignment. The column is E and the range
differs between rows 3 and 130 depending on number of participants. I have a
macro that selects all players in the column, I now need to randomly sort the
cells that are selected.

Thanks,
Guy

"Harlan Grove" wrote:

Guy wrote...
That doesn't seem to do anything. I am just a little better than a novice at
VBA code, if the code needs to be modified for my application, I can't figure
out how.
....

Begging the question why you're trying to do this.

You'd need to put the code in my previous response into a GENERAL VBA
module. For example, press [Alt]+[F11] to open the Visual Basic Editor
(VBE), then run the VBE menu command Insert Module. That should open
a window showing a new, empty general module. Paste my code into it.

Then press [Alt]+[F11] again to switch back to Excel. Right-click on
the form control you want to use to shuffle the selected cells'
values, and assign the macro foo to it.