Formula not filling down

Question

I am trying to fill down two before (A, and B) to the last row in column c. however, My code only insert the formula and doesn't fill down. if I continue to execute the code it will fill one line. then if I click execute again it will fill another line.

Sub row()

 Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Select

ActiveCell.Formula = "=year(today())" 'this will be inserted into the first empty cell in column B
ActiveCell.Offset(0, -1).Value = "Actual" ''this will be inserted into the first empty cell in column A

ActiveCell.FillDown
end sub

You're in the last cell of the column. What's Excel supposed to do? Also, read up on [how to avoid Select and Activate](https://stackoverflow.com/q/10714251/1188513). — Mathieu Guindon, Jan 25 '18 at 20:25
I data in column C but column A and B are blank. So I want to use that formula to insert those values into the empty Columns A and B to the last non-empty cell in column C — cookiemonster, Jan 25 '18 at 20:27
So if you have values in C1:C10, you want to put "Actual" in A1:A10 and "=year(today))" in B1:B10? Using FillDown you must specify a destination. — SJR, Jan 25 '18 at 20:38

score 2 · Accepted Answer · answered Jan 25 '18 at 20:41

Perhaps you mean this? You need to read up on Filldown as you are not specifying a destination range.

Sub row()

With Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
    .Offset(, -1).Formula = "=year(today())"
    .Offset(, -2).Value = "Actual"
End With

End Sub

score 0 · Answer 2 · answered Jan 25 '18 at 20:39

First, take Mat's Mug's suggestion from the comments and make sure you qualify which sheet/workbook you are calling the Cells method on. Then, try the code below. I believe FillDown will only work if there is something in the cells below to replace. Otherwise the function wouldn't know where to stop if it is filling empty cells. Instead, find the last used cell in column C and then blast the value/functions you want in all of the cells in rows A and B at once.

Sub row()

Dim wb As Workbook
Dim ws as Worksheet
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range

Set wb = ThisWorkbook
Set ws = wb.Worksheets("SheetNameHere") ' change this to an actual sheet name
Set rngA = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Set rngB = rngA.Offset(0,1)
Set rngC = ws.Cells(Rows.Count, 3).End(xlUp)

ws.Range(rngA,rngC.Offset(-2,0)).Value = "Actual" ''this will be inserted into ever empty cell in column A up to the last cell in Column C
ws.Range(rngB,rngC.Offset(-1,0)).Formula =  "=year(today())" 'this will be inserted into every empty cell in column B up to the last cell in Column C

End Sub

Formula not filling down

2 Answers2