Check value in Array

[Pradip Jain]

i have an array of unique numbers (say 50 rows and 1 column, all 50 cells
contain a unique number). I generate a new random number and want to check if
this new number is already present in this array. One way is to check cell by
cell to see if the new random number is equal to that cell value. this works
fine but is slow. I have to repeat this step several thousands time and it is
making a big difference in computation time. Is there a way by which i can
check the entire array at once to see if a particular value is present?

for example see my code below:

********************************
RandomRowLowerBound = 1
RandomRowUpperBound = 50


NewRandomStartLine:
Randomize
RandomRowGenerated = Int((RandomRowUpperBound - RandomRowLowerBound + 1) *
Rnd + RandomRowLowerBound)
For CounterNumber13 = 1 To RandomRowUpperBound
If RandomRowAlreadyGeneratedArray(CounterNumber13, 1) = RandomRowGenerated
Then
GoTo NewRandomStartLine
End If
Next CounterNumber13
RandomRowAlreadyGeneratedArray(CounterNumber14, 1) = RandomRowGenerated

********************************
Here the program generates a random number in between 1 and 50. Then it goes
on to check if that number has already been generated in a previous step. The
program has record of random numbers already generated in
RandomRowAlreadyGeneratedArray which is updated after each successful new
random number found. To repeat my query again - Is there a way by which i can
check the entire array at once to see if a particular value is present?

TIA

 

[paul.robinson]

Hi
Create a collection instead of an array to do your checking

Dim ArrayCollection as New Collection
'Populate the collection with unique array values
For i = 1 to 50
ArrayCollection.Add MyArray(i), Cstr(MyArray(i))
Next i
'Try to add a random number. An error is generated if it exists already
Err.clear
On error resume next
'maybe a loop here, in which case you will need another err.clear
ArrayCollection.Add YourRandomNumber, Cstr(YourRandomNumber)
If Err.Number<>0 then
msgbox "The number " & YourRandomNumber & " is in the array"
end if
on error resume next

regards
Paul

 

[Stefi]

Try this solution:

Option Base 1
Sub test()
RandomRowLowerBound = 1
RandomRowUpperBound = 50
Dim RandomRowAlreadyGeneratedArray(50)
For CounterNumber = 1 To RandomRowUpperBound
Randomize
On Error Resume Next
Do
talalat = False
RandomRowGenerated = Int((RandomRowUpperBound - RandomRowLowerBound
+ 1) * Rnd + RandomRowLowerBound)
talalat = WorksheetFunction.Match(RandomRowGenerated,
RandomRowAlreadyGeneratedArray, 0) > 0
Loop While talalat
On Error GoTo 0
RandomRowAlreadyGeneratedArray(CounterNumber) = RandomRowGenerated
Next CounterNumber
End Sub

Regards,
Stefi


„Pradip Jain†ezt írta:

 

[Dana DeLouis]

Hi. Just some thoughts. If you are trying to generate the integers 1-50,
your method needs, on average, to loop 224.9602 times.
For small problems like this (50 out of 50), you may prefer to just generate
the integers 1-50, and then just shuffle the array.
Collections are good if you want 50 numbers out of a larger pool. (like
1000).
Where the trade-off point is would be a personal choice.