Random Selection Code

[mcrooks1129]

Computer
PrinterCameraScannerUSBDell OptiPlex GX270PSC 2410PS 935SJ 8200Carry/480MDell Optiplex GX280nSDPS 1315PS R707SJ 7650H-E USBLenovo Kaitian M4800DJ 5740PS 960SJ 5530BelkinLegend PIII/667EBDJ 6540PS R817Fujitsu Scan Snap 5100EOXMacallyHP NetServer E200PSC 375PS R727Xerox Documate 262Radio ShackDell OptiPlex GX260PSC 240PS 927PSC 3310CentonClone Intel P-D 2.8PSC 8450PS 725SJ 4850PeracomClone AMD 64 OPTERON 242 1.6GHzEpson 300PS 960PS 960HP COMQPAQ dc7100 CMTHP color Laser 46650HP color Laser 46650Fujitsu Scan Snap S5000AMD 64 Althlong FX-55 2.6GHzPSC 2575PSC 2575SJ 8250HP COMQPAQ dc7100 CMTPSC C3100PSC C3100Gateway E-4650DJ 6122DJ 6122HP Pavilion 511wHP Photosmart 8450Canon PowerShot SD20 Elph digital cameraHP Pavilion 9880Dell Dimension4550Gateway SelectCompaq EvoCompaq D510 CTCompaq EvoCompaq EvoPavilion 8765cHP KayakDell XPS R400Compaq EvoDell XPS 7500Dell Dimension 8100HP Pavilion PrototypePavilion 533wHP Compaq D530 CMTHP PavilionHP X9300 64bit systemcompaq presario sr1620nxcompaq presario sa4000tHP pavillionhp media center m1160nAdaptec 7 port hubUH-204 4 port USB hubBelkin Hi-speed USB 2.0 7 port hubBelkin FSU224 4-port usb hubRadioShak 4-port usb hubRadioShak 4-port usb hubKensington Pocket hub 4 usb hubHP OmniBook 3000HP OmniBook 3000

Hi Guys,
I have this inventory table (above) with 5 different columns of items. What I need is a way to generate a random configuration using VBA. This means that I need 1 computer, 1 printer, 1 camera, 1 scanner and 1 USB chosen from each column and then placed on another sheet. Is there a way to do this? Any help would be much appreciated.

-Matt

[mdmackillop]

Hi Matt,
Welcome to VBAX
This should give you 50 random selections

Sub Randoms()
Dim Sel As Long, i As Long, Cnt As Long, Rw As Long
For Sel = 1 To 50
Randomize
For i = 1 To 5
Cnt = Sheets(1).Cells(Rows.Count, i).End(xlUp).Row
Rw = Int(Rnd() * Cnt) + 1
Sheets(2).Cells(Rows.Count, i).End(xlUp).Offset(1) = Sheets(1).Cells(Rw, i)
Next
Next Sel
End Sub


BTW If you need to post a spreadsheet, use Manage Attachments in the Go Advanced section
Regards
MD

[mcrooks1129]

Hey thanks man, it works perfect!

[mdmackillop]

Hey thanks man, it works perfect!
Whilst we can't always offer perfection, we do our best. :rofl:
Regards
MD