CODEWITHSUNDEEP
vbaexcel
cookiemonster
cookiemonster

Reputation: 368

Formula not filling down

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

Upvotes: 0

Views: 292

Answers (2)

SJR
SJR

Reputation: 23081

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

Upvotes: 2

user2731076
user2731076

Reputation: 813

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

Upvotes: 0

Related Questions