Forum Discussion

zaladrew1415's avatar
zaladrew1415
Copper Contributor
Oct 18, 2021

Dynamic use VBA to autofill

Hi Microsoft community,

 

I need some help with the following problem:

 

 

 

 

 

The Macro should select the first cell with the if function and autofill the complete range in the column AS until the last row that contain data in the cell next to it (Column R). Column Q & R are the cells used in the formula.

 

The situation looks like this:

 

Selection.End(xlToRight).Select

Range("AS1").Select ActiveCell.FormulaR1C1 = "Actual GBS"

Range("AS2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-28]=RC[-27],RC[-28],RC[-28]-RC[-27])"

Selection.AutoFill Destination:=Range("AS2:AS" & Range("Q" & Rows.Count).End(xlUp).Row) 
Range(Selection, Selection.End(xlDown)).Select

 

When I try to execute, it shows "Autofill method of Range class failed". I am not sure what is wrong with my code. 

 

I am looking for a way to make this works. It will help me greatly if anyone could advise. Thanks in advance.

 

 

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 18, 2021

    zaladrew1415 

    You can use this. It avoids selecting cells, which is generally more efficient.

        Dim m As Long
    Range("AS1").Value = "Actual GBS"
    m = Range("Q" & Rows.Count).End(xlUp).Row
    Range("AS2:AS" & m).Formula = "=IF(Q2=R2,Q2,Q2-R2)"

Resources