How to set up an autofill macro

Requirement:

The user would really appreciate it if someone could help. The user has recorded a macro that imports data from a text file, then manipulates the data, creates a new column and inserts a new formula into the column for more manipulation, the macro will work on files of different sizes but with same general structure, the problem the user is having is that the user is not sure how to create a code that would autofill the new formula to all the active rows (number of rows will differ), here's the code that the user has for this particular task, the range is static atm, could someone please help to make it dynamic depending on the number of active rows?

Code:

Range("AP2").Select
    ActiveCell.FormulaR1C1 = "=RC[-9]-RC[-40]"
    Range("AP2").Select
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.End(xlDown)), Type:=xlFillDefault
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-15

This code throws an error unfortunately.

Solution:

This macro assumes your initial formula is already posted in C2 & D2. Edit as required.

Code:

Option Explicit

'paste this in Routine Module
Sub dragformula()

    With Sheets("Sheet1")
        .Range("C2").AutoFill .Range("C2:C" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        .Range("D2").AutoFill .Range("D2:D" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        'add additional columns here
    End With

End Sub

Code:

Range("ap2:ap" & activesheet.Usedrange.specialcells(11).row).formula ="=RC[-9]-RC[-40]"

Code:

activecell.entireColumn.specialcells(2).formula="=RC[-9]-RC[-40]"

Obtained from the OzGrid Help Forum.

Solution provided by Logit and graha_karya.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.