title: open excel files copy contents to array output those items to another results file
body: <p>I have never coded in VBA but I'm trying to transpose some knowledge from obj-c into this.  </p>
  
  <p>-I want to open a file (about 200 files in a folder)</p>
  
  <p>-look through the range of cells in each file</p>
  
  <p>-then find everything (words) before the first comma in each cell (cells in this range have three commas)</p>
  
  <p>-add the value of each cell to an array</p>
  
  <p>(scan the remaining files and do the same)</p>
  
  <p>-take results array and paste them all in another file named master list</p>
  
  <p>I think I have covered most of this (first time with VBA though so not sure), but I haven't figured out how to read everything up to the first comma in each cell</p>
  
  <p>Also please let me know if I have any obvious errors, or logic issues</p>
  
  <p>Thank you in advance</p>
  
  <p>Thank you for your help!</p>
  
  <pre><code>Sub CopyWordsToMainFileRow()
  
  Dim Cell As Range
  Dim counter As Integer
  Dim word As String
  Dim arrayOfIngredients() As Variant 'array of words from search
  Dim fileName As String
  Dim arrayOfFileNames As Variant
  Dim MainCounter As Integer
  Dim p As String, x As Variant
  
  MainCounter = 0
  counter = 0
  
  ' Make array of file names
      p = "/Users/waf04/Desktop/*.xls"
      arrayOfFileNames = GetFileList(p)
  
      Select Case IsArray(arrayOfFileNames)
          Case True 'files found
              MsgBox UBound(arrayOfFileNames)
              Sheets("Sheet1").Range("A:A").Clear
              For i = LBound(arrayOfFileNames) To UBound(arrayOfFileNames)
                  Sheets("Sheet1").Cells(i, 1).Value = arrayOfFileNames(i)
              Next i
          Case False 'no files found
              MsgBox "No matching files"
      End Select
  'end make array of file names
  
  'Create array from cells in each file
  For fileNameCounter = 0 To UBound(arrayOfFileNames)
  
      fileName = arrayOfFileNames(MainCounter)
      Workbooks.Open fileName:="fileName"
      arrayOfIngredients = Range("AT2:EP200").Value 'add value of cells to array
  
      'make array of results for each file
      For Each Cell In Range("AT2:EP200")
          word = Cell.Value ' make this string equal to the value of everything before the first comma in that cell
          arrayOfIngredients(counter) = word 'add string to array
          counter = counter + 1
          Next Cell
  
  Workbooks.Close fileName:="fileName"
  Next fileNameCounter
  '==============================
  'Output unsorted array
  Workbooks.Open fileName:="/Users/waf04/Desktop/ingredients_collection.xlsx"
  Range("A1:A" &amp; UBound(arrayOfIngredients) + 1) = _
  WorksheetFunction.Transpose(arrayOfIngredients)
  
  End Sub
  </code></pre>