Email discussion lists for the UK Education and Research communities

## EXCEL-VB-DISCUSS@JISCMAIL.AC.UK

#### View:

 Message: [ First | Previous | Next | Last ] By Topic: [ First | Previous | Next | Last ] By Author: [ First | Previous | Next | Last ] Font: Proportional Font

#### Options

Subject:

Re: To pick n cells at random from a column

From:

Date:

Mon, 19 Mar 2001 18:23:46 +0800

Content-Type:

text/plain

Parts/Attachments:

 text/plain (105 lines)
 ```I haven't followed all of this thread, but here is some code that will generate 36 random numbers between 1 and 36. Then it will use the random numbers as row numbers to copy from Column A and paste over the top of the 36 random numbers in Column B. Sub RandomNumberGenerator() Dim Rw As Integer 'Clear the range ready for random numbers Range("B1:B36").Clear Randomize ' Initialize random-number generator. For Rw = 1 To 36 'Set the Row numbers  Cells(Rw, 2) = Int((36 * Rnd) + 1)   Do Until WorksheetFunction.CountIf _      (Range("B1:B36"), Cells(Rw, 2)) = 1     Cells(Rw, 2) = Int((36 * Rnd) + 1)   Loop Next Rw 'Do the random row pick For i = 1 To 36 Rw = Cells(i, 2) Cells(Rw, 1).Copy _ Destination:=Cells(i, 2) Next i End Sub Dave Hawley http://www.ozgrid.com ----- Original Message ----- From: Nadarajah Thirugnanasothy <[log in to unmask]> To: <[log in to unmask]> Sent: Monday, March 19, 2001 5:27 PM Subject: To pick n cells at random from a column : Hi, : : Still I have not finished my task. : : I would like to pick n different cells at random from column A and make : up another column from the selected n cells. : : My knowledge of excel and VB is another VB (very basic). This is how I : am thinking of accomplishing my task. Create a Macro in VB to do the : following. : Pick a cell at random, copy the content to an array and : delete that raw and compact the column. Then : for 2 to n : pick another cell at random add it to the array and : delete that raw and compact the column. : end for : Copy the array to another empty column. : : : I would very much appreciate if any one can help me to produce the : macro. : : Rgds : : Thiru : : --- Begin Forwarded Message --- : : Date: Mon, 12 Mar 2001 12:07:05 +0000 : From: Nadarajah Thirugnanasothy <[log in to unmask]> : Subject: To pick n cells at random from a column : Sender: [log in to unmask] : To: [log in to unmask] : Reply-To: Nadarajah Thirugnanasothy <[log in to unmask]> : Message-ID: <[log in to unmask]> : : : Hi, : : Could you please tell me how can I pick n cells at random from a column : of data? : : Many thanks : : Thiru : : : --- End Forwarded Message --- :```