View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Chip Pearson Chip Pearson is offline
external usenet poster
 
Posts: 7,247
Default EXCLUDING DUPES IN STRING ARRAY !!!!

I think the fastest method is to test for a value's existence in an
array is to use the Match function. For example, examine the
following code:

' <START CODE
Dim Arr(1 To 5) As String
Dim Ndx As Long
Dim B As String
Dim V As Variant

''''''''''''''
' load up some test values
For Ndx = 1 To 3
Arr(Ndx) = Chr(Asc("a") + Ndx - 1)
Next Ndx

''''''''''''''
B = "f" ' doesn't exist in Arr
V = Application.Match(B, Arr, 0)
If IsError(V) Then
' does not exist
Arr(Ndx) = B
Else
' exists, do nothing
End If

''''''''''''''
B = "b" ' exists in Arr
V = Application.Match(B, Arr, 0)
If IsError(V) Then
' does not exist
Arr(Ndx) = B
Else
' exists, do nothing
End If

''''''''''''''
' list content
For Ndx = LBound(Arr) To UBound(Arr)
Debug.Print Ndx, Arr(Ndx)
Next Ndx
' <END CODE

First, part of the array Arr is given some test values, "a", "b", and
"c". Then, B is assigned "f". The value "f" is searched for in Arr by
the Match function. The variant V holds the result of Match. If it is
an error (IsError = True), then "f" does not exist in the array and is
added to the array. It is assumed that at this point in the code, the
variable Ndx points to the first unused element of Arr. Next, the
value "b" is assigned to the variable B and again Match is used to see
if "b" exists in Arr. Since it does already exist, Match assigns its
position to V, and when V is tested for an error, IsError returns
False so we know "b" already exists.

Finally, the code just lists the content of Arr.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Sat, 10 Apr 2010 06:55:41 -0700, jay dean
wrote:

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