-3

I have 20 kids composing of 10 boys in column A and 10 girls in Column B . I need excel vba to choose a pair of boy and girl at random for the 20 kids.

thanks

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
rakesh seebaruth
  • 69
  • 1
  • 1
  • 11
  • Could you sort both alphabetically then adapt =Rand() formula to randomise selection? – LJ01 Mar 28 '18 at 06:01
  • why would you need VBA? – ashleedawg Mar 28 '18 at 06:01
  • Take your pick of off the shelf (code and formula) solutions google gives immediately: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2013_release/vba-code-for-generate-random-number-between-2/f662cdd8-a101-46a4-aeb8-fde4942f8985 – QHarr Mar 28 '18 at 06:10
  • Possible duplicate of [Repeating random variables in VBA](https://stackoverflow.com/questions/2884972/repeating-random-variables-in-vba) Just set two ranges (1 for boy, 1 for girl) – QHarr Mar 28 '18 at 06:12

2 Answers2

2

This is overkill, but I slapped it together just together just for fun. It selects a random name from any number of lists (one per column) including colors and sound while choosing.

I just hit double-backspace while about to submit my edit, losing it all, so this is the short version - less information and links.

img

Option Explicit  'always use this!

Const shtName = "RandVBA" 'name of worksheet
Const cellRange = "B5:C14" 'cell range (each column gets separate selection)

Public Declare Function Beep Lib "kernel32" _
   (ByVal dwFreq As Long, _
    ByVal dwDuration As Long) As Long


Sub chooseRandomColumns()
    'selects a random cells from each column in range, with colors and sound

    Randomize
    Dim cr As Range, col As Range, x As Long, n As Long, winners As String
    Set cr = Sheets(shtName).Range(cellRange)

    For x = 1 To 20
        cr.Cells.Interior.Color = RGB(255, 255, 255) 'reset background colors
        winners = "" 'erase winners names
        For Each col In cr.Columns
            n = Int(Rnd() * cr.Rows.Count) + cr.Row 'pick random cell in column
            Sheets(shtName).Cells(n, col.Column).Interior.Color = _
                RGB(Int(Rnd() * 256), Int(Rnd() * 256), Int(Rnd() * 256)) 'random color
            winners = winners & " - " & Sheets(shtName).Cells(n, col.Column) & vbLf
        Next col

        Beep Int(Rnd() * 2000) + 300, 125 'random beep 125ms/300-2300Hz
        DoEvents 'catch-up
    Next x

    MsgBox "The winners are:" & vbLf & vbLf & winners, vbExclamation, "Winners!"

End Sub

Download sample .XLSM workbook with these examples here. (File contains VBA/Macros so you may get a security warning when opening.)

Pick randomly from a list Without Excel

You can also do this online (completely without Excel) using RANDOM.ORG.

img

Pick randomly from a list with Excel (No VBA)

img

Formula in C3: 

=OFFSET(C5,RAND()*10,0)

Formula in D3:

=OFFSET(D5,RAND()*10,0)

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
0

You can do it without VBA as well -

see the formula in screenshot -

enter image description here

Aritesh
  • 1,985
  • 1
  • 13
  • 17