asked on

Extend formulas using macro

Hi,

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!

Regards,
Shanan

Shanan212

ASKER

    Range("W10000:AA10000").Select
    Selection.AutoFill Destination:=Range("W10000:AA10017"), Type:= _
        xlFillDefault

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!

StephenJR

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

ASKER CERTIFIED SOLUTION

Shanan212

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Shanan212

ASKER

Thanks experts!