excel vba - Return an Index number from a collection VBA -

if have created collection, can search search collection , return index number within collection?

due newbie status, can't post screenshots of i'm trying do, let me try explain i'm trying accomplish:

i have history log warehouse database in excel format several thousand lines long-- each line representing transaction of product moving in or out of many 10 different bins. goal identify possible different bins in thousands of lines, copy/transpose ~10 bins column headers, , go through each transaction , copy transaction quantity (+1,-3, etc) correct column, being able separate transactions , more identify , generate accounting of when product moved in/out of each respective bin. sort of pivottable, isn't how work.

here code working on far, comments. problem explained in last comment:

sub forensicinventory() dim binlocat collection dim rng range dim cell range dim sh worksheet dim vnum variant dim bincol integer dim actcol integer dim qtycol integer dim integer dim lastrow long dim x long  'this part used find relevant columns 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  = 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 bin column , builds collection of unique bins interested in. on error resume next     each cell in rng.cells         if len(cell.value) <> 8 , not isempty(cell)             binlocat.add cell.value, cstr(cell.value)         end if     next cell on error goto 0  'now take unique bin names , put them column header on same spreadsheet, starting in column 10, , spacing every 2 cells thereafter. each vnum in binlocat     cells(1, 10 + i).value = vnum     = + 2 next vnum      'here problem exists me.  code works , succeeds in copying qty     'to column 10, want determine index number of bin binlocat,     'and use index number place value under appropriate column header. x = 2 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 

in "for x = 2 lastrow" loop, need find way index number (1, 2, 3, etc.) searching bin in collection binlocat. binlocat, once created, static. envision like:

neededcolumn = binlocat.item(cells(x,bincol).value).index (pseudocode) 

then replace 10s in case stmt "neededcolumn" , work.

maybe taking wrong approach, seems me need collection able search portion efficiently. thoughts or links solution? based on i've read, elsewhere, think ability i'm describing not available, i'm not sure i've understood i've read collections far.

instead of using each loop, use for n = 1 binlocat.count loop - n index. or did misunderstand?