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
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.