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

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

Pradip Jain wrote:
> 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

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:

> 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

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


"Pradip Jain" <(E-Mail Removed)> wrote in message
news:8C9BB47F-3FFC-406C-B9B3-(E-Mail Removed)...
>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