How to fill Excel formula up dynamically, in upper direction?

Question

I am trying to fill out a formula up dynamically. I mean, without specifying the exact range.

I know where I am located in the Excel. That means, we can use ActiveCell. I just filled out the ActiveCell (AD24) with the formula (See screenshot below for more details). Now I want it to be dragged up to AD12. But without explicitally specifying AD12, because the file is evolving and it could be longer in the future. Here is the code I am using now. But it has AD12 and AD24, which are fix values and not dynamic.

Is there a way to just fill out the formula up to whatever row (upper direction) is filled out in the column just to the left of the cell where I am located?

ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-3]-1,""-"")"
Selection.AutoFill Destination:=Range("AD12:AD24"), Type:=xlFillDefault

Answer 1

I'm not sure i I understood properly but looks like you got a column with dynamic quantity of rows and you want to fill an adjacent column with a formula, based of that quantity of rows.

In this example, I want to apply a formula in the yellow column to all rows based on the left column:

You need to know at least one starting cell. In this case, I know my data will start always at cell C6. I don't know how many rows but the code will handle it:

Sub test()
Dim LR As Long
Dim StartingCell As Range
Dim MyFormula As String

Set StartingCell = Range("C6")

LR = StartingCell.End(xlDown).Row

'fill column to the right with formula
MyFormula = "=RC[-1]*100"

With StartingCell
    Range(.Offset(0, 1), .Offset(LR - .Row, 1)).FormulaR1C1 = MyFormula
End With

Set StartingCell = Nothing

End Sub

After executing code I get this:

The code will work even if the amount of rows is dynamic but must be consecutive (no blanks). If you got blanks you need a different approach.