Compare values of an Array in VBA and don't increment the array or add the value if it is a duplicate

Question

I'm trying to loop through a spread sheet of non consecutive values and read/compare the value to the rest of the values previously read into the array before incrementing the array dimension and adding the value to the array. Ill try to demonstrate with a little example below.

i.e.

Sub ArrayCompare()
Dim Cntry() As String
ArrayDim = 5 'The array is dimensioned with another counter that is not     pertinent to this question but typically not greater than 5 in 1 dimension
ReDim Cntry(ArrayDim)
Range("C1").Select
Dim Counter As Integer

Counter = 8 'In the real spread sheet the counter is dynamic, ive just put this in as an example

Do Until Counter = 0
    ArrayCounter = 0 'This is used to compare the array values Cntry(C0)

    Do Until ActiveCell.Value <> ""
    If ActiveCell.Value = "" Then
    ActiveCell.Offset(1, 0).Select
    Else: End If
    Loop

        If Active.Value = Cntry(ArrayCounter - 1) Or ActiveCell.Value = Cntry(ArrayCounter - 2) Or ActiveCell.Value = Cntry(ArrayCounter - 3) Or ActiveCell.Value = Cntry(ArrayCounter - 4) Then 'this doesn't work because the array is not dimensioned to this size yet. 
         ActiveCell.Offset(1, 0).Select
         Else
         Cntry(ArrayDim) = ActiveCell.Value
         ArrayDim = ArrayDim + 1
         End If
Counter = Counter - 1
Loop
End Sub

Look at the answers [HERE](http://stackoverflow.com/questions/5890257/populate-unique-values-into-a-vba-array-from-excel) one will work for you. — Scott Craner, May 19 '16 at 21:21

score 0 · Answer 1 · answered May 19 '16 at 21:46

Use Collections, they're are easier to work with. Here's example code where you loop through your collection of stored values and mark a flag if you find a duplicate. The same thing can be done with an array if you insist on using them

Sub Add_Value_If_Not_Duplicate()
Dim My_Stuff As New Collection
Dim End_of_Data, i, t As Integer
Dim Unique_Value As Boolean
End_of_Data = 13             'This will obviously be different for you
Unique_Value = True
Dim New_Value As Integer

'Loops through Column A of sheet 2 to demonstrate the approach
 For i = 1 To End_of_Data                            'Iterate through the    data in your excel sheet
   New_Value = Sheet2.Range("A" & i).Value         'Store the new value in a variable
 If My_Stuff.Count > 0 Then                      'If you have previously read values
    'Looping through previously recorded values
    For t = 1 To My_Stuff.Count
        If My_Stuff(t) = New_Value Then
            Unique_Value = False    'If value already exist mark a flag
            Exit For
        End If
    Next
Else
                                    'If you have no previously read values
End If
'Add if Unique
If Unique_Value = True Then                     'If value isn't already listed then add it
    My_Stuff.Add (New_Value)
End If
Unique_Value = True                             'Reset your
Next
    For i = 1 To My_Stuff.Count
        Sheet2.Range("B" & i) = My_Stuff(i)  'Printing to demonstrate
    Next
End Sub

Compare values of an Array in VBA and don't increment the array or add the value if it is a duplicate

1 Answers1