Josh Ord-Hume


I am trying to create an Excel macro that will look at the contents of cells A1, B1 and C1 and 'randomize' their order. So, if the contents of A1, B1 and C1 are "1", "2" and "3" respectively, they might end up as "2", "3", "1" or "1", "3", "2" once the macro has been run. Any ideas gratefully received...
Many thanks,
Josh



Re: randomizing the contents of Excel cells

PYA_Ferro


Hi! Try this code:

Code Block

Sub MiSub()

Dim MiRand, Temp

Randomize

MiRand = Int((3 * Rnd) + 1) 'Number between 1 and 3
Temp = Cells(1, 1).Value 'Store "A1" value
Cells(1, 1).Value = Cells(1, MiRand).Value
Cells(1, MiRand).Value = Temp

MiRand = Int((3 * Rnd) + 1)
Temp = Cells(1, 2).Value 'Store "B1" value
Cells(1, 2).Value = Cells(1, MiRand).Value
Cells(1, MiRand).Value = Temp

MiRand = Int((3 * Rnd) + 1)
Temp = Cells(1, 3).Value 'Store "C1" value
Cells(1, 3).Value = Cells(1, MiRand).Value
Cells(1, MiRand).Value = Temp

End Sub

Hope it helps you.

Cheers.






Re: randomizing the contents of Excel cells

Mezhick

You can use this code. But it will proceed for long time (if you will use large ranges).

Code Block
Function Randomizer(r As Range)
Dim cCell As Range
Dim iRow As Integer
Dim iCol As Integer
Dim rCount As Integer
Dim cCount As Integer
rCount = r.Rows.Count - 1
cCount = r.Columns.Count - 1
ReDim a(rCount, cCount) As Variant
Randomize
iRow = Round(rCount * Rnd(), 0)
iCol = Round(cCount * Rnd(), 0)
For Each cCell In r.Cells
Do While Not a(iRow, iCol) = Empty
iRow = Round(rCount * Rnd(), 0)
iCol = Round(cCount * Rnd(), 0)
Loop
a(iRow, iCol) = cCell.value
Next
End Function

The faster function needs more coding. You need to create your own RND function Smile

PS. It is quite slow. But if you would make your own Round(rCount * Rnd(), 0) function, that will pass used iRow and iCol pair, then you'll get a prefect result.







Re: randomizing the contents of Excel cells

Josh Ord-Hume

Dear PYA Ferro,
Thank you very much for this snippet... with a little bit of tweaking, I have managed to get it to do exactly what I need it to do. I wonder if I could ask you to further test your VBA mastery with a little variant of my original request The solution that you have proposed randomizes cells in a pre-defined selection of cells. How might one go about creating a macro that randomizes cells in a user-defined selection I am envisaging a scenario in which a user would select a range of cells and then hit a "randomize!" button...
Many thanks once again.... your help is appreciated!

Josh





Re: randomizing the contents of Excel cells

PYA_Ferro

Hi Josh!
Sorry for the long time to reply (and for my English too haha, here we go).

Code Block

Sub MiRandSub()

Dim RandRow, RandCol, Temp, TotalCols, TotalRows, i, j
Dim MiRange As Range

If TypeName(Selection) <> "Range" Then
MsgBox ("You must select a valid range of cells")
Else
Application.ScreenUpdating = False

Randomize

TotalCols = Selection.Columns.Count

'Number of Columns inside the range selected by the user

TotalRows = Selection.Rows.Count

'Number of Rows inside the range selected by the user

Set MiRange = Selection 'Store the range selected by the user

For i = 1 To TotalRows
For j = 1 To TotalCols
RandRow = Int((TotalRows * Rnd) + 1)

'Number between 1 and TotalRows


RandCol = Int((TotalCols * Rnd) + 1)

'Number between 1 and TotalCols

Temp = MiRange.Cells(i, j).Value
MiRange.Cells(i, j).Value = MiRange.Cells(RandRow, RandCol).Value
MiRange.Cells(RandRow, RandCol).Value = Temp
Next
Next

Application.ScreenUpdating = True
End If

End Sub

Then you create the "randomize!" button in your spreadsheet, and link it with the Sub.

The user select some cells and press the button.

Hope this helps you, good luck!





Re: randomizing the contents of Excel cells

Josh Ord-Hume

Please don't apologise! Your English seems perfectly understandable... and your sub works brilliantly!
I changed :
If
TypeName(Selection) <> "Range" Then

to
If Selection.Count < 2 Then

Thank you again for your help and expertise. It is hugely appreciated!

Josh





Re: randomizing the contents of Excel cells

PYA_Ferro

Glad to hear/read that!.

Just as a suggestion:

If the user can only select ranges, it's ok, but if there are (or maybe will be in the future) any shape/chars/img/etc in your sheet, then I suggest you to try this:

Code Block

If TypeName(Selection) <> "Range" or Selection.Count < 2
Then

Try adding a shape to your sheet, then select it and press de "randomize!" button, and you will see what I mean.

Good luck!