# Re: Check value in Array

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.
--
HTH :>)
Dana DeLouis
Windows XP & Office 2003

news:8C9BB47F-3FFC-406C-B9B3-01489E069340@xxxxxxxxxxxxxxxx
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
Then
GoTo NewRandomStartLine
End If
Next CounterNumber13

********************************
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

.

## Relevant Pages

• Re: Index from TM
... Microsoft Excel MVP ... And the intersection of my chosen cell and the A1:A9 range was an ... These arguments can be either defined, empty or omitted. ... The array_ argument has been defined, ...
(microsoft.public.excel.worksheet.functions)
• Re: Index from TM
... And the intersection of my chosen cell and the A1:A9 range was an ... These arguments can be either defined, empty or omitted. ... The array_ argument has been defined, ... If the indexed array was C22:C25 the relative sequential positions would be ...
(microsoft.public.excel.worksheet.functions)
• RE: User List Box - List from Hidden range - VBA worng
... In your for loop, ... range is only 1 column wide this will be a single cell). ... Basic Editor type the word on it's own in lower case on a line and hit enter. ... in the array reference - and an array starts with element 0. ...
(microsoft.public.excel.programming)
• Re: Do I need a two dimensional array for this?
... I think you can use the same array that you use for ICE. ... The new part will start a loop and look for IC100 and QA100 in the range. ... It will write PK100 to the cell and increment i by 1. ...
(microsoft.public.excel.programming)
• Re: Wrong data type returned from user-defined function
... If you want it to function as an array function then it needs to be ... Dim strFormula As String ... UnRound = CVErr ... would execute just as if I'd typed it in the cell myself. ...
(microsoft.public.excel.programming)