title: Build Array in Excel VBA
body: <p>Desired result: The result would be a function that builds an array of values from column B. The values are restricted by having the same value in column A. 'E.g. Column A value = 1 myArray = (0,1,2)' 'Column B Value = 2 myArray = (4,5,6,7,8)' </p>
  
  <p>First time trying to use arrays in VBA and need help.  I have the following data in columns A and B in Excel:</p>
  
  <pre><code>A   B
  1   0
  1   1
  1   2
  2   4
  2   5
  2   6
  2   7
  2   8
  3   9
  3   10
  3   11
  4   12
  4   15
  4   18
  </code></pre>
  
  <p>I have the following VBA Code:</p>
  
  <pre><code>Function buildMyArray()
  
      Dim ARange as Range
      Dim B as Integer
      Dim myArrary as Variant
  
      For Each ARange In Sheets("SheetName").Range("B:B")
          If ARange.Value = 1 Then
              B = Application.WorksheetFunction.VLookup(ARange.Value, Sheets("SheetName").Range("A:B"), 2, False)
              myArray = Array(B)
          End If
      Next ARange
  
  End Function
  </code></pre>
  
  <p>I am trying to build a function that will search column A for each instance of an integer (which I will eventually dynamically pass).  Then for each instance of that integer do a vlookup on the adjacent column (B).  I then want to create an array of the values from the lookup (in the above case (0,1,2)).  </p>
  
  <p>The above code is the closest I have gotten.  I get returned the first value in B (0) as the only value in the array.  I am reasonably confident this is because the array logic is in the If statement.  However, if I move it out of there I get compile errors.</p>
  
  <p>Please help a novice learn.  Thanks.</p>