I need to copy a formula from one cell into a block of cells. I have tried using AutoFill, which works to a degree, but it does not populate the first row or the first column.
My formula in C4 should be copied to the entire block C4:X12. (In real life it is a sumproduct.)
Sub GetUniqueLocationsTEST() Dim LastRow As Long Dim LastCol As Long With thisWorkbook.Worksheets("sheet2") LastRow = .Cells(.Rows.Count, "B").End(xlUp).row LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column .Range("C4").formula = "=1 1" .Range("C4").AutoFill Destination:=.Range("C4:X12") ', .Cells(LastRow, LastCol)) End With End Sub
Results in C4 showing “2”, and the range D5:X12 correctly showing “2”.
BUT D4:X4 and C5:C12 are blank – nothing is copied there! If I put other random text into these cells it is still there after the Sub is run.
In the sample code you’ll see that I’ve even forced the destination range to a literal.
Perhaps Autofill is not the correct method? Should I be using Copy? Or something else?
Thanks in advance.