Here is a method that should be faster than what I have posted previously...
this is the code I mentioned in my other response (to myself)...

Dim X As Long, StartPosition As Long, ArrayIndex As Long
Dim B As String, CheckString As String, Arr() As String
'....
'....
ReDim Arr(1 To SomeMaxIndex)
CheckString = String(200000, Chr(1))
StartPosition = 2
ArrayIndex = LBound(Arr)
For X = 1 To SomeMaxIndex
'
' Some kind of conditioning code goes here I presume
'
If YourCondition Then
If InStr(CheckString, Chr(1) & B & Chr(1)) = 0 Then
Arr(ArrayIndex) = B
ArrayIndex = ArrayIndex + 1
Mid(CheckString, StartPosition) = B
StartPosition = StartPosition + Len(B) + 1
End If
End If
Next
ReDim Preserve Arr(1 To (ArrayIndex - 1))

There is one drawback to this method though, you have to estimate the
maximum number of characters that could be in the CheckString variable. To
do this, you need to be somewhat familiar with your data. Let's say the
longest text string you expect to have is 19 characters long and that you
expect to have about 10,000 unique text strings when you are done processing
your data. Add one to the maximum number of characters and then multiply in
order to get the upper limit (I called it SomeMaxIndex in my code above) to
Dim your Arr array to...

SomeMaxIndex = (19 + 1) * 10000

which is how I got my estimate of 200000 in my CheckString assignment
statement above. Also note that the StartPosition will always be 2 (we need
a Chr(1) in front of the CheckString text).

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
You could maintain what I would call a "check string" for this purpose.
Let's say the name of this String variable is CheckString. Then you can do
this in a loop...

For X = 1 To SomethingLessThanInfinity
'
' Some kind of conditioning code goes here I presume
'
If YourCondition Then
If Instr(CheckString, Chr(1) & B & Chr(1)) = 0 Then
Arr(X) = B
CheckString = CheckString & Chr(1) & B & Chr(1)
End If
Next

If the text value in variable B is not in CheckString, then this is the
first time you have seen its value, so assign it to the array and then
store its value, with a delimiter on both sides of it, into CheckString. I
have used Chr(1) as my delimiter because under normal circumstances it
will not appear in any of the text being assigned to B during the loop.
You can use any character (or characters) that you **know** for certain
will never appear in your text strings for the delimiter. The reason you
need this delimiter is to stop accidental substring finds crossing over
between your B values. For example, if two consecutive values being
assigned to B during the loop were "moth" and "error" and did not use a
delimiter between them, then they would go into the CheckString as
"...motherror..." and the latter assignment of "mother" to B would
register as already having been added to the array... the delimiters
guarantee this won't happen.

--
Rick (MVP - Excel)



"jay dean" wrote in message
...
Hi -

B is a string var. In my code, if a certain condition is met, then store
B in th next available index of Arr(). However, before I store B, I need
to check that the current value of B does not already exist in Arr().

Is there a "faster" way to accomplish this, or I need to loop from
lbound(Arr) to Ubound(Arr) every time to check if the new value to be
stored already exists?

Thanks
Jay Dean



*** Sent via Developersdex http://www.developersdex.com ***