arrays - Excel VBA Add strings to a list, and sort alphabetically and numerically -

here goals latest project i'm working on. in text file, there information different tools.

1. parse text file , extract number , string. done. although, in final build, need to parse through clipboard.

2. put these 2 items list of sort. have tried attempting make dynamic multi-dimensional array left tool number , right toolpath name. couldn't arrays work, tried collections. problem collections there no sort function, need. learned dictionaries, sparked idea may missing use. 3 things available?

3. use list determine item #2 should used , determine position in list is. use information tasks.

4. use item #3 , remove #2 list.

5. repeat goal 2-4 until items have been removed.

so cnc machining, when saving toolpaths. normally, user organize order of tools should go first, second, etc., automatically. know how navigate menu using vba, need figure out how organize information excel can all.

my theory step 3 use collection in main list, , search collection term in correctorder collection. if finds in main list, take action. if not, continue down correctorder collection.

so basically, need ideas on should use this: collections, arrays, dictionaries, or else.

thanks byron, decided use excel list method , sorting. reason avoiding using excel sheets because wanted reduce amount of information on sheet , use temporary list. lot easier , more friendly use excel made do. here code reads text file, extracts need, , sorts it. other sub uses list text file define item in list goes first, second, , on.

sub autotoolpathretrieve() dim textline string, toolnumber string, comments string, long, greencount long, robcount long, greentools string, robtools string, greentool boolean, robtool boolean  greencount = 0 robcount = 0 greentools = "" robtools = "" greentool = false robtool = false  x = 1 8 range("i" & x) = " " next range("j31:j33").clearcontents range("z1:aa50").clearcontents open "v:\cnc dept\art cam\will b\will programs\autotoolpath.txt" input #1 on error resume next = 0 savedvar = 0 until eof(1) line input #1, textline debug.print (textline)  'check line ":" , extract line select case mid(textline, 1, (instr(1, textline, ":")) - 1)     case "tool"         debug.print ("contains tool")         toolnumber = mid(textline, 22, 2)         select case true             case mid(toolnumber, 2, 1) <> "]"                 toolnumber = mid(toolnumber, 2)                 range("b2").value = "green"                 greencount = greencount + 1                 greentool = true                 call greenstyle             case mid(toolnumber, 2, 1) = "]"                 toolnumber = replace(toolnumber, "]", "")                 range("b2").value = "red or blue"                 robcount = robcount + 1                 robtool = true                 call robstyle         end select         select case toolnumber             case 5                 range("j31") = mid(textline, 25, 6)             case 6                 range("j32") = mid(textline, 25, 6)             case 7                 range("j33") = mid(textline, 25, 6)         end select          range("i" & toolnumber) = toolnumber      case "comments"     = + 1         debug.print ("contains comments")         comments = mid(textline, 21)         range("aa" & i).value = toolnumber & " " & comments          select case true         case greentool = true                 greentools = greentools & vbnewline & toolnumber & " " & comments                 greentool = false         case robtool = true                 robtools = robtools & vbnewline & toolnumber & " " & comments                 robtool = false         end select      case "machining time"     select case         case = 0     debug.print ("contains machining time")         cb = mid(textline, 21)           select case cb           case left(cb, 2) = 0 range("e37") = mid(cb, 4, 2) range("f37") = mid(cb, 7)           case else range("e37") = mid(cb, 4, 2) + (left(cb, 2) * 60) range("f37") = mid(cb, 7)           end select           end select end select  select case true case = 0 , left(textline, 3) = "v:\"     debug.print (mid(textline, 21, 27))     debug.print (mid(textline, 18, 11))     debug.print (mid(textline, 35, 20))     debug.print (mid(textline, 21, 13))     select case true         case mid(textline, 21, 27) = "production programs 9-05-07"             range("b33") = "production programs"         case mid(textline, 18, 11) = "2_customers"             range("b33") = "customer file"         case mid(textline, 35, 20) = "replacement programs"             range("b33") = "replacement programs"         case mid(textline, 21, 13) = "custom sheets"             range("b33") = "custom sheets"     end select     savedvar = 1 end select  select case true case > 0 , savedvar = 0             msgbox "notice: file has not been saved. please save current artcam file continue.", vbokonly             close #1             exit sub end select loop close #1      select case true     case greencount <> 0 , robcount <> 0         select case true             case greencount > robcount                 msgbox "error: red , blue machine tools used in program! tools are: " & robtools & vbnewline & "fix , try again.", vbexclamation                 exit sub             case greencount < robcount                 msgbox "error: green machine tools used in program! tools are: " & greentools & vbnewline & "fix , try again.", vbexclamation                 exit sub         end select end select  sleep (100) range("aa1:aa50").sort _ key1:=range("aa1"), order1:=xlascending call autotoolpathcompare end sub sub autotoolpathcompare() dim textline string, n long n = 1 range("z1:z50").clearcontents open "v:\cnc dept\art cam\will b\will programs\autotoolpathorder.txt" input #1 until eof(1) line input #1, textline debug.print (textline) x = 1 50 select case range("aa" & x)     case <> "" select case textline case = range("aa" & x) range("z" & x) = n debug.print (n) n = n + 1 end select end select next loop close #1 end sub