title: Excel VBA - Dynamic Size of Arrays and Element Assignment
body: <p>How do I size arrays dynamically while trying to assign values to individual elements of the array from the sheet? In columns A and B I have </p>
  
  <pre><code>A   B
  1   Houston
  2   Miami
  3   New York
  4   Toronto
  5   Los Angeles
  </code></pre>
  
  <p>I want the VBA to determine the number of elements and size the arrays based on how many elements are there. Then, the defined array gets the values from column B assigned to the elements. In the code below I am trying a For loop to get the values and assign them to each of the elements. </p>
  
  <p>Here is the code I have:</p>
  
  <pre><code>Sub getNames()
  
  Dim n As Integer 'denotes the number of elements
  Dim i As Integer 'index
  Dim Name() As String
  Dim flag As Boolean
  
  'Initialize values
  i = 0
  n = 0
  flag = True
  
  'For loop to determine number of elements
  While flag = True
    'check if the current cell has data in it
     If Cells(i + 1, 1) &lt;&gt; "" Then
          i = i + 1
     Else
          flag = False
     End If
  Wend
  
  n = i
  
  
  ReDim Name(n)
  
  For i = 1 To n
  Name(i) = cells(i,2).value
  Next i
  
  End Sub
  </code></pre>
  
  <p>However, I keep getting Syntax Error when trying to assign the value from the Cell. </p>