Log In
Register
Home
New Member
Excel for Finance
September 20, 2019
Offline
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
See Answer
Trusted Members
February 13, 2021
Offline
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.
Trusted Members
February 13, 2021
Offline
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! 
Trusted Members
Moderators
November 1, 2018
Offline
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
Answers Post
1 Guest(s)
