title: How to Populate Multidimensional Array in Excel Macro using For Each Loop With Formula?
body: <p>I want to populate Array in VBA , using for each-loop but unable to do that</p>
  
  <pre><code>Dim MyArray() As Variant
  Dim RowCounter As Integer
  Dim ColCounter As Integer
  Dim rCell As Range
  Dim rRng As Range
  Set rRng = Sheet1.Range("B10:Z97")
  RowCounter = 0
  ColCounter = 0
  ReDim MyArray(rRng.Rows.Count, rRng.Columns.Count)  'Answer by @varocarbas
  For Each rCol In rRng.Columns
     For Each rCell In rCol.Rows
      If IsNumeric(rCell.Value) And (Not (IsEmpty(rCell.Value))) And (Len(rCell.Value) &lt;&gt; 0) Then
           'ReDim Preserve MyArray(RowCounter, ColCounter) -- Old Logic which cause Error
           MyArray(RowCounter, ColCounter) = rCell.Value
           RowCounter = RowCounter + 1
      Else
          'Debug.Print rCell.Value &amp; " is not an Integer" &amp; vbNewLine
      End If
    Next rCell
    ColCounter = ColCounter + 1
    RowCounter = 0
  Next rCol
  </code></pre>
  
  <p>But <code>ReDim Preserve MyArray(RowCounter, ColCounter)</code> in this line I got <strong>subscript error</strong> , when <code>ReDim Preserve MyArray(1, 0)</code></p>
  
  <p>I want to read the value from excel sheet populate the array then do some calculation and update the value of Last Cell of the each Column in Excel from by Calculate Value of the Excel.</p>
  
  <p><strong>Update in code</strong> </p>
  
  <pre><code>Function RunSquareOfVariance(temperature As Integer, cellValue As Variant) As Double
           RunSquareOfVariance = "=IF((" &amp; temperature + cellValue &amp; ")&lt;0,0,(" &amp; temperature + cellValue &amp; "))*IF((" &amp; temperature + cellValue &amp; ")&lt;0,0,(" &amp; temperature + cellValue &amp; "))"
  End Function
  </code></pre>
  
  <p>If <strong>within the code</strong> I change the bellow line </p>
  
  <p><code>MyArray(RowCounter, ColCounter) = RunSquareOfVariance(StantardTemperature, rCell.Value)</code></p>
  
  <p>Now within <strong>MyArray(0,0)</strong> Value store As <code>=IF((-16.8)&lt;0,0,(-16.8))*IF((-16.8)&lt;0,0,(-16.8))</code> </p>
  
  <p>But I want to store the value of the formula Withing <code>MyArray(0,0) = ValueOftheFormula</code> </p>