Extend formulas using macro
Posted on 2011-10-06
I have tons of formulas on nearly 20 tabs (each consisting 30 col x 1000 rows). Half of the tables are not populated with user data but the formula is there in anticipation so that if a user enters something, the fields would be populated accordingly.
The problem is, my file is now 55mb. I want to reduce it and done some research. I've seen one way is to reduce redundant formulas.
Is there a way to extend formula above based on a cell in a column being populated?
Such that, write a macro to see if A1 is populated, then the macro would pull down B1, C1, D1, etc which would house seperate formulas
I understand you can get the formula into VBA (dynamic) but I want to see if above is possibile?
If then any directions is much appreciated!
I've got the following code (please see below) but I want to see if this can be done live. Especially, in accordance with the user input (say user inputs 800 rows, then the formula goes down 800 rows)