Problem copy formula

Hi All,
when I want to extend a formula in a range of rows below the cell where the formula has been set, I have this error

Runtime error '1004'
autofill method of range class failed

I use this VBA code

Sheets("Sheet3").Range("BF8").Formula = Sheets("Sheet1").Range("I64").Formula
Selection.AutoFill Destination:=Range("BF9:BF12"), Type:=xlFillDefault
Range("BF9:BF12").Select

My intention is to have for each row a formula that is adapted for the row where it is set.

i.e IF A1 = x (row 1)
IF A2 = x (row 2)

Any idea?
Thanks in advance for your kind support.

Giovanni

Welcome to the forum. :)

The VBA helpfile gives a clue why you get that error:


Parameters
Name Required/Optional Data Type Description
Destination Required Range The cells to be filled. The destination must include the source range.
Type Optional XlAutoFillType Specifies the fill type.




So it should be fine if the destination range intersects with the source range, eg:

Sheets("Sheet3").Range("BF8").Formula = Sheets("Sheet1").Range("I64").Formula
Sheets("Sheet3").Range("BF8").AutoFill Destination:=Sheets("Sheet3").Range("BF8:BF12"), Type:=xlFillDefault


However, you don't need to use the Range.Autofill() method here. You can use the Range.Formula property for the whole range and you should find that the row references are automatically incremented for you (assuming they are not absolute, in which case autofilling wouldn't increment them either). This reduces the code to just:

Sheets("Sheet3").Range("BF8:BF12").Formula = Sheets("Sheet1").Range("I64").Formula


Hope that helps...

Thanks a lot!

Giovanni