asked on

Excel Macro - Fill Down Formula

Hi,

I have the following macro which fill down the formula in cell F2 down to the last row of the table, working great... but.... on the same sheet, I also need to fill down formulas on Cell L2 and N2 down to the last row.

My Macro look like this:

Sub FillDownRAM()
Sheets("Inventaire").Select
Range("F2").Select
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown
End Sub

But I'm a bit lost when it's time to add other column .... Can you help?

Saurabh Singh Teotia

You can use the following code to do what you are looking for..

Sub FillDownRAM()
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
    Sheets("Inventaire").Select

    Range("F2:F" & lrow).Formula = "Your formula here"
    

End Sub

Open in new window

Saurabh...

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

You don't need VBA to do this. If your data is in a Table then make sure that you have formatted it as a Table. See

Overview of Excel Tables

You can even set autofill without a Table using Excel Options but the Table is best

mldaigle1

ASKER

Hi Roy_Cox,

Presently, i am manually double-clicking to fill down the formulas every single time that I have to get the inventory.

Since this is a repeatitve task i was thinking of saving time by using an additional macro in that workbook since i am already using many macros.

:- /

mldaigle1

ASKER

sktneer,

Unfortunately, it does not work.

Find attach a sample of the inventory.
inventory.xlsx

Saurabh Singh Teotia

mldaigle1,

Did you try my approach of applying the formula directly??

Saurabh...

Roy Cox

You've missed the oint. An excel Table has calculated fields i.e. the formulas auto populate, read the article that I posted a link to.

I've edited your workbook to create a Table. Now select a cell in the last row and Tab throgh that Row , when you tab to the last cell it will automatically add a new row with all formulas
inventory.xlsx

Subodh Tiwari (Neeraj)

What didn't work?

If copied the formula in F2, L2 and N2 down the rows. Please see attached.

Or am I missing something?
inventory.xlsm

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mldaigle1

ASKER

Thanks Roy_Cox,

I will check more closely the article and the new file.

Thanks sktneer,
Your macro is not working fine when i change the offset to -1 everywhere

Have a great day Experts!!

:)

/mld.

Subodh Tiwari (Neeraj)

Offset(0,-1) means, one column left to the active cell.

You're welcome mld!

Roy Cox

Check it out, you will find Tables are extremely useful when working with the data. Writing formulas is so much easier