Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    152
    Thanks
    69
    Thanked 0 Times in 0 Posts

    Using VBA to Scramble Rows of Data for Quiz

    Hi Experts,

    Thanks for everything you do to help with Excel questions.

    I have a worksheet with 100 plus rows of data from columns A to J consisting of questions for a quiz.

    I would like to scramble the rows so the order of the questions can be changed.


    Thanks!

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm sure you'll get an interesting and clever VBA solution. I had something similar to do without VBA.

    In Sheet2, I used =rand() in column A, then =rank(A1,$A$1:$A$100) in column B, then =indirect("Sheet1!A"&$B1) in column C.

    Fill down as many rows as you have and then fill across from column C.

    I filled the formula from column C across the columns as far as needed (you said J, so fill across 10 columns). But the A in the indirect formula needs to be changed to B, C, etc.
    OR do something like this (through J):
    =INDIRECT("Sheet1!"&CHOOSE(COLUMN()-2,"A","B","C","D","E","F")&$B1)

    If you don't want the values continually changed, convert the A column to values after you do it.

    Clip0001.jpg

    (I don't know how to remove the incorrect attachment)
    Attached Images Attached Images
    Last edited by kweaver; 2016-02-22 at 19:48.

  3. The Following 2 Users Say Thank You to kweaver For This Useful Post:

    Excelnewbie (2016-02-23),Maudibe (2016-02-22)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    KW,

    An interesting and clever VBA solution would only be to code what you have already demonstrated in the most efficient randomizing technique using formulas. Nicely done!

    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-02-23)

  6. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Another way to deal with the changing columns, instead of using CHOOSE where the column letters are specified explicitly, is to use INDIRECT with R1C1 type of addressing.

    For example,
    INDIRECT("Sheet1!R" & B1 & "C" & COLUMN()-2, FALSE)

    This way, as you fill from col C to the right, the col will keep changing. You don't have to worry about how many columns there are.

    Fred

  7. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Nice, Fred. I forgot about the last argument used with INDIRECT.

    If you're not careful, you learn something new every day! YAY

  8. #6
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    152
    Thanks
    69
    Thanked 0 Times in 0 Posts
    I am leaving town and won't be able to try this approach out until I get back next week. I want to express my gratitude to everyone before leaving. Thank you!
    Last edited by Excelnewbie; 2016-02-23 at 18:19.

  9. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Yay for learning!

    about the only thing I can take credit for on the R1C1 (or called just RC?) addressing is not forgetting something that I just did a few days earlier!

    I had replied to another post and used that as part of the solution.

    Had it gone much longer to this thread, the thought would have been gone.

    You know, at this age, memory is the 2nd thing to go.

    Fred

  10. #8
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    152
    Thanks
    69
    Thanked 0 Times in 0 Posts
    Experts,

    Thanks for your help.

    I found the following code and wanted to pass it on for all to use.

    Public Sub Shuffle()

    Dim lCnt As Long
    Dim rRng As Range

    Set rRng = Sheet1.Range("A2:J30") 'RB-Enter range here

    'Record which row it starts on
    With rRng.Columns(4)
    .Formula = "=ROW()"
    .Value = .Value
    End With

    Do
    'Add a random value for sorting
    With rRng.Columns(4)
    .Formula = "=RAND()"
    .Value = .Value
    End With

    'Sort on random value
    Sheet1.Sort.SortFields.Clear
    Sheet1.Sort.SortFields.Add rRng.Columns(4), xlSortOnValues, xlAscending
    With Sheet1.Sort
    .SetRange rRng.Offset(-1).Resize(rRng.Rows.Count + 1)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
    End With

    lCnt = lCnt + 1
    'if any rows are the same as the starting row
    'do it again
    Loop Until ShuffleComplete(rRng.Columns(4)) Or lCnt > 100

    Debug.Print lCnt

    End Sub

    Public Function ShuffleComplete(rRng As Range) As Boolean

    Dim rCell As Range
    Dim bReturn As Boolean

    bReturn = True

    For Each rCell In rRng.Cells
    If rCell.Value = rCell.Row Then
    bReturn = False
    Exit For
    End If
    Next rCell

    ShuffleComplete = bReturn

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •