Databases
StackOverflow2013
Postsdbo
POExcel VBA Calculate Method not update Array Formulas; showing as #N/A
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA Calculate Method not update Array Formulas; showing as #N/A
    text
    copied!<p>I have a series of array formulas in Excel that key off of each other. These are automatically resized to fit a range of data that is generated via a proprietary Excel add-in. </p> <p>However, whenever my code rewrites some of the array formulas to the correct new size, the cells all show as #N/A until either you edit another unrelated cell on the sheet, save the sheet, or press F9. </p> <p>Using code such as Application.Calculate, ActiveSheet.Calculate, etc do not have any effect.</p> <p>However, using SendKeys "{F9}" does. </p> <p>As an example, these are two formulas on the sheet:</p> <pre><code>={IF(LEN(INDEX(A:A, ROW()))&gt;0,ROW(A:A)+2)} </code></pre> <p>and</p> <pre><code>={LARGE(OFFSET($J$1,0,0,ROW()),1)} </code></pre> <p>The first formula works fine after writing it programmatically to a range of cells. It merely tells me the row number of a cell that has text in it. </p> <p>The second formula does not work after writing it programmatically to a range of cells. It gives me the largest row number that has been previously seen in a list of numbers (which is the output of the first formula). If I press F9, the second formula updates correctly. If I do Application.Calculate in VBA, nothing happens. I've also tried the various other recalculate methods available at the Worksheet level as well, but no luck. </p> <p>Has anyone encountered something like this before? </p> <p><strong>edit:</strong> The resize code essentially boils down to something like this (stripping out all of the support code that allows me to make more generalized calls to it):</p> <p>First, I do:</p> <pre><code>formula = dataSheet.Cells(startRow, startColumn).formula </code></pre> <p>Then later:</p> <pre><code>Set DeleteRange = dataSheet.Range(dataSheet.Cells(startRow, startColumn), dataSheet.Cells(bottomBound, rightBound)) DeleteRange.ClearContents Set DeleteRange = Nothing </code></pre> <p>Then later on:</p> <pre><code>Set resultRange = dataSheet.Range(dataSheet.Cells(startRow, startColumn), dataSheet.Cells(startRow + Height - 1, startColumn + Width - 1)) resultRange.FormulaArray = formula Set resultRange = Nothing </code></pre> <p>In a nut shell, I make a copy of the formula, clear the range, then rewrite it. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload