vba AutoFill method of range class failed

Question

I am trying to find month of a date in column A and paste the result to column B. They both have headers, column A’s is Date and column B’s is Month. I would like to have vba codes simply calculating the month of column A.

Here are my codes so far,

Sub Month()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
ws.Cells(2, "B") = "=Month(A2)"
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").AutoFill Destination:=Range("B2:B" & lastrow), Type:=xlFillDefault
End Sub

But I keep getting AutoFill method of range class failed error and I tried to change the AutoFill type but it won’t work. If you guys know a faster way to do it, let me know, too. (not excel functions, plz)

Thank you,

What worksheet is *active* when you run this code? And what is the `Selection` object when you run this code? And is the sheet protected in any way? — David Zemens, Nov 03 '16 at 14:44
I tried both but they they are still giving me the same error. I think I was trying to select ws.Cells(2, "B") = "=Month(A2)" — sc1324, Nov 03 '16 at 15:12
I modified the codes but it's still not working, having the same error. — sc1324, Nov 03 '16 at 16:02
this should work. thanks for the comments and idea. I fixed it. it works now — sc1324, Nov 03 '16 at 18:49

score 1 · Answer 1 · edited May 23 '17 at 12:33

This code will fail if the value of lastrow is 2. You need to add some logic to account for that. Also revised to use better method of finding "last row"

Sub month()
Dim ws As Worksheet
Dim lastrow As Long
Dim startCell As Range

Set ws = Worksheets("sheet1")
With ws
    Set startCell = .Cells(2, 2) ' or .Range("B2")
    lastrow = startCell.EntireColumn.Find("*", AFter:=startCell, SearchDirection:=xlPrevious).Row
    If lastrow = startCell.Row Then lastrow = lastrow + 1
    startCell.Formula = "=Month(A2)"
    startCell.AutoFill Range(startCell, startCell.Cells(lastrow)), xlFillDefault
End With
End Sub

score 0 · Answer 2 · answered Nov 03 '16 at 14:48

"The destination must include the source range". Refer the link. So please select the cell. If you want to apply formula to cell use .Formula = "=Month(A2)"

Sub Month()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    ws.Cells(2, "B") = "=Month(A2)"
    ws.Cells(2, "B").Select
    Selection.AutoFill Destination:=Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault
End Sub

vba AutoFill method of range class failed

2 Answers2