title: Why would Cells(x,y).Value return empty for non-empty cells in Excel VBA?
body: <p>I have an Excel VBA function. In a loop, I use <code>Cells(x,y).Value</code> to return the value of certain cells. But sometimes that returns empty, even the cell is not empty.</p>
  
  <p>One example in my worksheet is <code>Cells(11,4).Value</code>. So I placed both <code>Cells(11,4)</code> and <code>Cells(11,4).Value</code> in Watches.</p>
  
  <p>In Watches, drilling down into <code>Cells(11,4)</code> shows <strong>Text</strong> to be "42.71%", and <strong>Value2</strong> to be "0.42708218178176", which is what I would expect.</p>
  
  <p>But also in Watches, <code>Cells(11,4).Value</code> still shows "Empty".</p>
  
  <p>Adding to the mystery, if I delete another watch, then <code>Cells(11,4).Value</code> will suddenly now be populated!</p>
  
  <p><a href="https://i.stack.imgur.com/vbfEI.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vbfEI.gif" alt="enter image description here"></a></p>
  
  <p>The obvious questions are:</p>
  
  <ul>
  <li>Why?</li>
  <li>What can I do to stop <code>Cells(11,4).Value</code> returning Empty?</li>
  </ul>
  
  <p><strong>Edited to add:</strong>
  The VBA is lengthy and irrelevant (i.e. if I strip it back to a single line, I get the same result).</p>
  
  <p>More relevant is the formula contained in cell D11 (the cell I'm interested in). It's this:
  =<code>(((D8+D9)/D5)^(1/(DATEDIF(D3,IF(D6&gt;0,D6,LOOKUP(2,1/(D18:D9998&lt;&gt;""),$B18:$B9998)),"d")/365.25))-1)</code></p>
  
  <p>That <code>IF</code> function returns two possible values: <code>D6</code> or <code>LOOKUP(2,1/(D18:D9998&lt;&gt;""),$B18:$B9998)</code>. (The latter is intended to get the value from column B for the last value in column D.)</p>
  
  <p>If I put a value in <code>D6</code> (so the <code>IF</code> statement doesn't do the LOOKUP), then <code>Cells.Value</code> returns the correct result. If there's no value in <code>D6</code>, and the <code>IF</code> statement returns the <code>LOOKUP</code> formula, then <code>Cells.Value</code> will return <code>Empty</code>.</p>