Randomize List VBA

Question

I'm fairly new to VBA.

I'm trying to randomize a list with VBA. The list has two headers "Name" and "Dials". I want to try to randomize the list with a macro then applying it with a button. I've tried using the code below but and it randomizes the names and numbers but doesn't keep them together. Meaning If My name is Jon and I had 3 dials, it moves my dials some where else. Any help would be appreciated.

Thanks,

Sub Random()
        Dim tempString As String
        Dim tempInteger As Integer
        Dim i As Integer
        Dim j As Integer

        For i = 1 To 5
          Cells(i, 2).Value = WorksheetFunction.RandBetween(0, 1000)
        Next i

    For i = 1 To 5
        For j = i + 1 To 5

      If Cells(j, 2).Value < Cells(i, 2).Value Then

        tempString = Cells(i, 2).Value
        Cells(i, 2).Value = Cells(j, 2).Value
        Cells(j, 2).Value = tempString
        tempInteger = Cells(i, 2).Value
        Cells(i, 2).Value = Cells(j, 2).Value
        Cells(j, 2).Value = tempInteger

      End If

        Next j
    Next i
End Sub

Possible duplicate of [Why is the first random number is always the same?](https://stackoverflow.com/questions/17046713/why-is-the-first-random-number-is-always-the-same) — Moacir, Oct 10 '17 at 18:03
Also, if you have 'a' list how do you have two headers? Seeing the data would help. — QHarr, Oct 10 '17 at 18:12
add another column to the list and fill that column with random values. then sort the list using that column. — jsotola, Oct 10 '17 at 18:33
You used [this Tutorial](http://www.excel-easy.com/vba/examples/randomly-sort-data.html), that is for just one column. It is similar to what jsotola said. — danieltakeshi, Oct 10 '17 at 18:49

barvobot · Answer 1 · 2017-10-11T01:31:39.890

4

Like @jsotola said, sorting seems the easiest way to do this:

Sub Randomer()
Dim i As Long, startRow As Long, endRow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set ws = ActiveSheet

startRow = 2
endRow = WorksheetFunction.Max( _
    ws.Cells(ws.Rows.Count, 1).End(xlUp).Row, _
    ws.Cells(ws.Rows.Count, 2).End(xlUp).Row)

For i = startRow To endRow
    Randomize
    ws.Cells(i, 3).Value = WorksheetFunction.RandBetween(1, 1000)
Next i

ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("C" & startRow & ":C" & endRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ws.Sort
    .SetRange Range("A" & startRow & ":C" & endRow)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ws.Range(ws.Cells(startRow, 3), ws.Cells(endRow, 3)).ClearContents

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

edited Oct 11 '17 at 01:31

answered Oct 10 '17 at 19:12

barvobot

887
1
7
17

Is there any way to make this in a loop so that it keeps checking as long as the rows have data? – Oct 10 '17 at 20:19
I edited my answer so that it will run the loop for as many rows have data (see `endRow = ...`), is that what you were looking for? – barvobot Oct 10 '17 at 20:27
Yes, Thanks. I was trying to do the loop myself but couldn't quite wrap my head around it. – Oct 10 '17 at 20:47
If this answer works for you, consider 'Accepting' it so other users with the same issue can find the answer more easily: see stackoverflow.com/help/accepted-answer – barvobot Oct 11 '17 at 12:26

Randomize List VBA

1 Answers1