Working with random numbers

Ok so:

I am trying to create a row of randomly chosen integers between 1 and 9... however there is a catch... a number must not be repeated. Here's what I've come up with so far:

Public Sub RandomSpot()

Rem Introduce a nrandom integer 1-9 into cell (1,1)
Rem "Rnd()*10" creates a random number from 0 to 1 then multiplies by 10
Rem to get a random number from 0 to 10.
Rem The INT command truncates decimals, creating an Integer.
Rem Since there is a chance that number will be either a 0 or a 10,
Rem I have the computer recalculate a number until a number between 1 and 9 is chosen

Do until 1 <= Sheet1.cells(1,1) <=9
Sheet1.Cells(1,1)=INT(Rnd()*10)
Loop


Rem Now, I wish to create numbers in the remaining columns of row 1
Rem however, they cannot be the same as the value in any other column.

""This is where I get stuck... what type of loop would this be? And how would I tell the computer to verify the next number's uniquness?


Basically I am trying to program a loop which will place a number 1-9 in the adjacent columns, but first it checks the other cells in the row to ensure that the number hasn't already been used.

As it stands, my code is sometimes producing the same number in a row.

I am not sure of the error.

Please help

-Jeff

This is probably worth a look:

http://www.cpearson.com/excel/RandomNumbers.aspx

Dom

Welcome to the forum, Jnels.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.