I have a macro that creates numbers 1 through 10 in a random order, in cells D1:D10. The code goes as follows.
Sub Draw()
Dim i As Integer, Number As Integer
Range("D1:D10").ClearContents
For i = 1 To 10
Do
Number = Int(Rnd * 10) + 1
Loop Until Range("D1:D" & i).Find(What:=Number, LookAt:=xlWhole) Is Nothing
Range("D" & i) = Number
Next i
End Sub
This works fine. But when I hide that column, it fails. Some numbers appear twice. When I open that column, everything goes right.
Can anyone explain this behaviour? And, how to solve this?
Wim de Groot, the Netherlands
I am just learning, but my theory, and mind you it is only a theory, is this:
You are using a DO Loop until the number is nothing, well, you've hidden the cells so the display is effectively nothing. And now my question before I totally give horribly wrong advice and thoughts is, why are you combining a DO loop and a FOR loop? If you delete the DO loop and just use the FOR shouldn't that work just fine?
For i = 1 to 10
Number = Int(Rnd * 10) + 1
range("D" & i) = number
Next i
I hope if I'm totally off base, someone will correct me. Like I said - I'm still learning.
Or, to keep the format that obviously works with the one minor hiccup, do the loop until it comes across an empty string?
Sub Draw()
Dim i As Integer, Number As Integer
Range("D1:D10").ClearContents
For i = 1 To 10
Do
Number = Int(Rnd * 10) + 1
Loop Until Range("D" & i) = ""
Range("D" & i) = Number
Next i
End Sub
I hope this helps, these are just ideas that I would start with if I had come across this issue. My knowledge is very basic, though, and I don't want to cause you more work. Someone check me, I welcome correction! 
You could use something like COUNTIF instead of Find:
Sub Draw()
Dim i As Integer, Number As Integer
Dim DataRange As Range
Set DataRange = Range("D1:D10")
DataRange.ClearContents
For i = 1 To 10
Do
Number = Int(Rnd * 10) + 1
Loop Until WorksheetFunction.CountIf(DataRange, Number) = 0
DataRange.Cells(i) = Number
Next i
End Sub
@ Jessica, Tanks for your answer, but your suggestion will just add any number between 1 and 10, it does not give UNIQUE numbers.
