Solved: Drag down formula via VBA

[debauch]

Hello,

Hoping someone can help. I have data in multiple columns that will change and the amount of rows will change also. Generally, I would jsut program in VBA a filldown method where possible. However, I need to filldown based on Column A and the formula is farther to the right.

Is there anyway to insert a formula into a cell and have it loop through until it sees the end of row A? I attached a sample w/ comments to explain further what I mean.

[Bob Phillips]

Sub FillDown()
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("E1").AutoFill .Range("E1").Resize(LastRow)
End With
End Sub


BTW, why use SUMPRODUCT, why not just A*B?

[debauch]

I'm getting run-time error '1004' when using example above. I should have mentioned the data columns are actually in a pivot table in the actual work file. I'm just trying to calculate to the next column of pivot data.

[debauch]

who-hoo! The data started on row 5, I just needed to modify it a bit. Thanks XLD.

P.s - the sumproduct was just a sample. I have your suggestion in the work file. THnks.