title: Using an array as an active list with unique values in VBA for Excel data
body: <p>I am trying to figure out how I can use VBA to create an array from Excel data as an active list that can have unique entries added and removed automatically as my script runs through a loop. </p>
  
  <p>Example:</p>
  
  <pre><code>Object#   ,  Status     ,   Group#  ,  Time            
  1      ,     Associate     , 1        , 1  
  1      ,     Associate     , 1        , 1.1  
  1      ,     Associate     , 2        , 2   
  1      ,     Associate     , 3        , 3  
  1      ,     Disassociate  , 2        , 4
  </code></pre>
  
  <p>The array would populate unique combinations of <code>Object</code>, <code>Status</code>, and <code>Group</code> but <code>Time</code> would not matter because once an object is associated it will remain associated until it is disassociated. </p>
  
  <p>I have looked for help on this but most posts only discuss populating the array and do not discuss how a loop could help to automatically remove an entry when it is disassociated.</p>
  
  <p>So in this example I would want an system that would allow me to enter the object # and time then the script would run and at the end it would tell me that "At time 4, object 1 is associated with groups 1 and 3". An alternate scenario would be "At time 3, object 1 is associated with groups 1, 2, 3". Finally, if at time 5 all objects were disassociated the the message would display the last group the object was associated to. </p>
  
  <p>I have a code that does everything I need until it runs into a situation where an object is associated to more than one group then it fails to return accurate information. My programming knowledge is limited so your help is appreciated. Below is the code I have currently where Cells (15, 8) and (18, 8) are value input cells for Object # and Time.</p>
  
  <pre><code>Private Sub CommandButton2_Click()
  Dim Association As String, i As Integer, Group As Integer
  
  Count = Application.WorksheetFunction.CountA(Range("A:A"))
  
  For i = 1 To Count 
  
      If Cells (i, 1).Value = Cells(15, 8) And Cells (i, 4).Value &lt;= Cells (18, 8) And Cells (i, 2) = "Associate"  Then Association = "Associated" 
  
      If Cells (i, 1).Value = Cells(15, 8) And Cells (i, 4).Value &lt;= Cells (18, 8) And Cells (i, 2) = "Disassociate"  Then Association = "NOT Associated"
  
      If Cells (i, 1).Value = Cells(15, 8) And Cells (i, 4).Value &lt;= Cells (18, 8) And Cells (i, 2) = "Associate"  Then Group = Cells(i, 3)
  
  Next i
  
      If Association = "Associated" Then MsgBox Association &amp; " Associated to " &amp; Group
      If Association = "NOT Associated" Then Msgbox Association &amp; " Was Last Associated to " &amp; Group
      If Association = "" Then Msgbox "Object Does Not Exist Prior to This Time"
  
  End Sub
  </code></pre>