Extend formulas using macro

Posted on 2011-10-06
Medium Priority
Last Modified: 2012-08-13

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)

Thank you!

Question by:Shanan212
  • 3
LVL 13

Author Comment

ID: 36924267
    Selection.AutoFill Destination:=Range("W10000:AA10017"), Type:= _

Open in new window

While waiting, I experimented with macros and found out that the above works.

I do want to see if this can be done live. If then, how? Thanks!
LVL 24

Expert Comment

ID: 36924293
Maybe not perfect, but have you checked whether (in Excel 20030 Tools > Options > Edit > Extend data range and formula is ticked?
LVL 13

Accepted Solution

Shanan212 earned 0 total points
ID: 36924400
Sub FormulaFiller()  
Dim rgCopy As Range, rgDelete As Range, rgFill As Range  
Set rgCopy = [E2:H2]    'Formulas in these cells will be copied down  
Set rgFill = [A2]       'First piece of raw data  
Set rgFill = Range(rgFill, rgFill.Worksheet.Cells(Rows.Count, rgFill.Column).End(xlUp))  'All data in that column  
Set rgFill = rgCopy.Resize(rgFill.Rows.Count)       'Fill formulas down in this range  
Set rgDelete = rgCopy.Offset(1, 0).Resize(rgCopy.Worksheet.UsedRange.Rows.Count)  
rgCopy.AutoFill rgFill, xlFillCopy  
End Sub 

Open in new window

The above is what I was looking for from this thread!

LVL 13

Author Closing Comment

ID: 36947358
Thanks experts!

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question