title: Excel VBA running out of Memory but there is plenty of memory
body: <p>i have this code:</p>
  
  <pre><code>Sub reportCreation()
  
  Dim sourceFile As Variant
  Dim wbSource As Workbook
  Dim wbDest As Workbook
  Dim sourceSheet As Worksheet
  Dim destSheet As Worksheet
  Dim rng As Range
  Dim i As Long
  Dim NValues As Long
  
  
  If sourceFile = False Then
      MsgBox ("Select the MyStats file that you want to import to this report")
      sourceFile = Application.GetOpenFilename
      Set wbSource = Workbooks.Open(sourceFile)
      Set sourceSheet = wbSource.Sheets("Test Dummy Sheet")
      Set rng = sourceSheet.Range("A:N")
      rng.Copy
  
      Set wbDest = ThisWorkbook
      Set destSheet = wbDest.Sheets("MyStats")
      destSheet.Range("A1").PasteSpecial
      Application.CutCopyMode = False
      wbSource.Close
  End If
  
  NValues = destSheet.Cells(destSheet.Rows.Count, 2).End(xlUp).Row
  
  With destSheet
      For i = 6 To NValues
         ' Cells(i, 3).NumberFormat = "0"
          With Cells(i, 3)
              .Value = Cells.Value / 1000000
              .NumberFormat = "0.00"
          End With
      Next i
  End With
  End Sub
  </code></pre>
  
  <p>the code runs fine for the IF Statement part which is a simple cop and paste sort of scenario but then once the WS has been copied to the new WB i need column 3 to devide any cell in that is larger than 1M by 1M and as soon as the code finds the first cell with a value of over 1M i get an error message "Runtime Error 7, system out of memory" but i still have 2GB left of memory so this does not seem to be your tipycal out of mem issue where i need to close a few applications and it will run because it just does not.
  i am wondering if there is an issue with my code?</p>
  
  <p>some of the sample values that the code will look are:</p>
  
  <pre><code>16000000
  220000
  2048000
  230000
  16000000
  230000
  16000000
  </code></pre>