Posted 27 December 2017, 8:15 am EST
I am running into an issue when updating cell formulas in large quantities. I have implemented code to auto fill down a column when the bottom right corner of a cell/range is double clicked. My code takes over 2 minutes to fill in about 3000 cells. This should not take any time at all. The issue I am running into occurs when trying to update the cells with formulas. For example I type a vlookup formula into a single cell on the first row of my sheet that references a second sheet. Then I am double clicking on the bottom right corner of this cell to trigger filling all the cells below it with their corresponding formula. I am able to calculate the correct formula in relation to the original cell for each of these cells with no lag at all. I have isolated the time problem to my line of code that reads:
cell.formula(newFormula);
Each time that line is called it takes about 60ms and when called 3000 times this degrades performance. Paint is suspended when I call to update the cells formulas’. Are there any suggestions to speed this up or is there anything you guys can do to speed up setting a cell’s formula?
