Excel 2007 : Using Rand in Macros?

[qcity]

Hey all,

Is there a way to encorporate RAND into a Macro? I want to shuffle up some files in my excel sheet so that the values of one of my columns is completely random. Here's my code so far.

Sub Jumble()
Dim cellVal

Range("F2").Select
Do Until Selection.Offset(1, 0).Value = ""
' Change this to offset 0, 0 when final code is written.
cellVal = ActiveCell.Value
ActiveCell.Value = ActiveCell.Offset(1, 0).Value
ActiveCell.Offset(1, 0).Value = cellVal
Selection.Offset(0, 1).Value = "Activecell swapped with position 1 in the list."
Selection.Offset(1, 0).Select
Loop
Range("A1").Select
End Sub

Basically, it walks through the loop, switching two values. Is there a way to make the offset a different value instead of a static one? Also, is it possible to say on my offset (0, 1) that the activecell swapped with a specific cell? Thanks for the help.

[protonLeah]

I don't quite understand what you're after, but the below will put a random integer in the first twenty cells of column A:

Please Login or Register  to view this content.

[qcity]

My goal with this program is to shuffle up the order of one of my columns.

For example, the column A has the following names:

Joe
Mary
John

I run the macro and this is the new order

John
Mary
Joe

I run it again and the result will be different.

What I'm asking for is if there's a way to store a random number and use it in one of my offset commands like:

ActiveCell.Value = ActiveCell.Offset([random number here], 0).Value

[protonLeah]

Does this help?

Please Login or Register  to view this content.

[qcity]

Didn't quite work. Shuffling did indeed happen but the numbers reached cell 20 when they ideally would stop at cell 15.

Will play around with it though. I think it's on the right track.

[qcity]

Fixed it. Here's the code:

Sub Jumble()
Dim cellVal
Dim RndRow As Long
Dim lowerbound As Long
Dim upperbound As Long

'replace x and y with the first and last row numbers of the list
lowerbound = 1
upperbound = 13

Range("F2").Select
Do Until Selection.Offset(1, 0).Value = ""
' Change this to offset 0, 0 when final code is written.
upperbound = upperbound - 1
RndRow = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
cellVal = ActiveCell.Value
ActiveCell.Value = ActiveCell.Offset(RndRow, 0).Value
ActiveCell.Offset(RndRow, 0).Value = cellVal
Selection.Offset(0, 1).Value = "Activecell swapped with position 1 in the list."
Selection.Offset(1, 0).Select
Loop
Range("G18").Select
ActiveCell.Value = RndRow
End Sub

Just needed subtracting when I walked down the list.
One last thing, though. I know this is possibly a very programming-noob question but how do I display what that row swapped into?

like...
Activecell.Value = "This cell swapped with position" RndRow

It doesn't work, though.

[protonLeah]

Insert where appropriate:

Please Login or Register  to view this content.