title: Loop through column in Excel and add unique string to array
body: <p>I am trying to write a program in VBA at the moment which is to be run in Excel. I am quite stuck right now because I am not very familiar with VBA and doing a search doesn't come up with my specific problem. </p>
  
  <p>I have a column in Excel which has 20,000+ hostnames for PC's on our network. What I need to do is be able to start at A2 and get the data in that cell, parse out the 5th and 6th characters only and check if those two characters are in an array called VariantDepartments. If the characters are in the array, I need to move to A3 and do it again. If the characters are not in the array, I need to add them to the end of the VariantDepartments array and then add those two characters plus the word "Workbook" to another array called DepartmentWorkBookNames at which point I index both arrays +1 and move to A3.</p>
  
  <p>This is what I am working on right now and it does not work:</p>
  
  <pre><code>Sub VulnerabilityMacroFinal()
  Dim VariantDepartments As Variant
  Dim departments As Variant
  Dim Department As String
  Dim VariantAssetTypes As Variant
  Dim AssetTypes As Variant
  Dim AssetType As String
  Dim Property As String
  Dim FileName As String
  Dim PropArray() As String
  Dim strFile As String  
  
  'Opening file &amp; getting property name
  strFile = Application.GetOpenFilename
  If strFile &lt;&gt; "False" Then Workbooks.Open strFile
  FileName = ActiveWorkbook.Name
  PropArray = Split(FileName, "-")
  Property = PropArray(0)
  
  'Setting asset types
  VariantAssetTypes = Array("PC", "Server", "Other Assets")
  
  'Program Start
  Sheets("AllVulnerabilities").Select
  
  'sorting out unnecessary types
  ActiveSheet.UsedRange.AutoFilter Field:=5, Criteria1:=Array( _
  "01-Adobe", "02-Apache", "06-Chrome", "09-Firefox", "13-Java", "16-Microsoft", _
  "38-VNC"), Operator:=xlFilterValues
  
  'Selecting the whole sheet
  Cells.Select
  'Creating sheets for different asset types
  For Each AssetTypes In VariantAssetTypes
  'Making variable a C String to make it easier to check in If statements
  AssetType = CStr(AssetTypes)
      If AssetType = "PC" Then
          'Parsing out the non local PC assets
          ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=Property &amp; "D*"
          ActiveSheet.Range("A:A,B:B,C:C,D:D,E:E,F:F").AutoFilter Field:=1
      ElseIf AssetType = "Server" Then
          'Selecting original sheet
          Sheets("AllVulnerabilities").Select
          'Parsing out the non local Server assets
          ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=Property &amp; "*", _
          Operator:=xlAnd, Criteria2:="&lt;&gt;" &amp; Property &amp; "D*"
      ElseIf AssetType = "Other Assets" Then
          'Selecting original sheet
          Sheets("AllVulnerabilities").Select
          'Parsing out the non local Server assets
          ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:="&lt;&gt;" &amp; Property &amp; "*"
      End If
      'Copying all info on sheet
      ActiveSheet.UsedRange.Copy
      'Selecting new sheet
      Sheets.Add.Name = Property &amp; " " &amp; AssetType
      'Selecting new sheet
      Sheets(Property &amp; " " &amp; AssetType).Select
      'Pasting data to new sheet
      ActiveSheet.Paste
      'Removing unnecessary colums
      Range("A:A,B:B,D:D,G:G,H:H,J:J,K:K").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
      'Auto adjusting column widths
      Range("A:A,B:B,C:C,D:D,E:E,F:F").EntireColumn.AutoFit
      ActiveWorkbook.Worksheets(Property &amp; " " &amp; AssetType).Copy
      'Close Workbook withoutsaving
      ActiveWorkbook.Close savechanges:=False
  Next AssetTypes
  
  Sheets(Property &amp; " PC").Select
  'THIS IS WHERE THE ARRAY SHOULD BE CREATED.
  
  For Each departments In VariantDepartments
  Department = CStr(departments)
      Sheets(Property &amp; " PC").Select
      'Parsing out the non local assets for EH
      ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=Property &amp; "D" &amp; Department &amp; "*"
      'Copying all info on sheet
      ActiveSheet.UsedRange.Copy
      'Selecting new sheet
      Sheets.Add.Name = Property &amp; Department
      'Selecting new sheet
      Sheets(Property &amp; Department).Select
      'Pasting data to new sheet
      ActiveSheet.Paste
      'Auto adjusting column widths
      Range("A:A,B:B,C:C,D:D,E:E,F:F").EntireColumn.AutoFit
      ActiveWorkbook.Worksheets(Property &amp; Department).Copy
      'Close Workbook withoutsaving
      ActiveWorkbook.Close savechanges:=False
      'Set PC Worksheet to be unfiltered
      Worksheets(Property &amp; " PC").ShowAllData
  Next departments
     'Completed
     ActiveWindow.Close savechanges:=False
  'Message box which appears when everything is done
  MsgBox "Done!"
  </code></pre>
  
  <p>End Sub</p>