title: Return an Index number from a collection VBA
body: <p>If I have created a collection, can I search search the collection and <strong>RETURN THE INDEX</strong> number from within the collection?</p>
  
  <p>Due to my newbie status, I can't post screenshots of what I'm trying to do, so let me try to explain what I'm trying to accomplish:</p>
  
  <p>I have a history log from a warehouse database in Excel format that is several thousand lines long-- each line representing a transaction of product moving in or out of as many as 10 different bins.  My goal is to identify all the possible different bins in the thousands of lines, copy/transpose those ~10 bins to column headers, and then go through each transaction and copy the transaction quantity (+1,-3, etc) to the correct column, thus being able separate the transactions and more easily identify and generate an accounting of when product moved in/out of each respective bin.  This would <em>sort of</em> look like a PivotTable, but that isn't really how it would work.</p>
  
  <p>Here is the code I am working on so far, with comments.  My problem is explained in the last comment:</p>
  
  <pre><code>Sub ForensicInventory()
  Dim BINLOCAT As Collection
  Dim Rng As Range
  Dim Cell As Range
  Dim sh As Worksheet
  Dim vNum As Variant
  Dim BINcol As Integer
  Dim ACTcol As Integer
  Dim QTYcol As Integer
  Dim i As Integer
  Dim lastrow As Long
  Dim x As Long
  
  'This part is used to find the relevant columns that will be used later
  BINcol = ActiveSheet.Cells(1, 1).EntireRow.Find(What:="BINLABEL", LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
  ACTcol = ActiveSheet.Cells(1, 1).EntireRow.Find(What:="ACTION", LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
  QTYcol = ActiveSheet.Cells(1, 1).EntireRow.Find(What:="QUANTITY", LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
  
  lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  
  i = 0
  Set sh = ActiveWorkbook.ActiveSheet
  Set Rng = sh.Range(sh.Cells(2, BINcol), sh.Cells(Rows.Count, BINcol).End(xlUp))
  Set BINLOCAT = New Collection
  
  'This next section searches the bin column and builds the collection of unique bins that I am interested in.
  On Error Resume Next
      For Each Cell In Rng.Cells
          If Len(Cell.Value) &lt;&gt; 8 And Not IsEmpty(Cell) Then
              BINLOCAT.Add Cell.Value, CStr(Cell.Value)
          End If
      Next Cell
  On Error GoTo 0
  
  'Now I take those unique bin names and I put them into a column header on the same spreadsheet, starting in column 10, and spacing every 2 cells thereafter.
  For Each vNum In BINLOCAT
      Cells(1, 10 + i).Value = vNum
      i = i + 2
  Next vNum
  
      'Here is where the problem exists for me.  This code works and succeeds in copying the QTY
      'to column 10, but what I really want to do is determine the index number of the bin from BINLOCAT,
      'and use that index number to place the value under the appropriate column header.
  For x = 2 To lastrow
    Select Case Cells(x, ACTcol).Value
        Case "MOVE-IN"
          Cells(x, 10).Value = Cells(x, QTYcol).Value
        Case "MOVE-OUT"
          Cells(x, 10).Value = -Cells(x, QTYcol).Value
        Case Else
    End Select
  Next x
  
  End Sub
  </code></pre>
  
  <p>In the "For x = 2 to lastrow" loop, I need to find a way to get the INDEX number (1, 2, 3, etc.) from searching for the bin in collection BINLOCAT.  BINLOCAT, once created, is static.  I envision something like:</p>
  
  <pre><code>neededcolumn = BINLOCAT.item(cells(x,BINcol).value).index (pseudocode)
  </code></pre>
  
  <p>Then I would replace the 10s in the Case Stmt with "neededcolumn" and this would work.</p>
  
  <p>Maybe I am taking the wrong approach, but it seems to me like I need the collection to be able to do the search portion efficiently.  Any thoughts or links to a solution?  Based on what I've read, elsewhere, I think that this ability as I'm describing it is not available, but I'm not sure I've understood everything I've read about collections thus far.</p>