excel - VBA copy formula into last row with data (based on different column) not working

Question

I can't get this to run even after trying all sort of possibilities for declaring and instantiating the variable LastRow. I want to pull down the formula results of column M only until the last row with data based, on column G (which would be row 23). The formula now gets dragged down until row 224.

Analogously, I need to pull down formulas for columns N and O based on the last row with data in column H (which would be row 24). Here, also, the formula gets pulled down to 224 instead of row 24.

What am I doing wrong? I know that for columns N and O I will need to create a second variable, based on column H, but want to get one running first and then try the second.

Dim LastRow As Long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 8).End(xlUp).Row

Range("M2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-6]/'Sheet1'!R1C2,0)"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-6]/'Sheet1'!R1C2,0)"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-3]/'Sheet1'!R1C2,0)"

Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M2" & LastRow)
Range("M3:M" & LastRow).Formula = "=G3&"",""&L3"

Range("M2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-6]/'Sheet1'!R1C2,0)"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-6]/'Sheet1'!R1C2,0)"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-3]/'Sheet1'!R1C2,0)"

Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M2" & LastRow)

Range("N2:O2").Select 'changed from M2 to N2

Selection.AutoFill Destination:=Range("N2:O2" & LastRow) 'changed from M2 to N2

'Range("N2:O2" & LastRow).FillDown
Range(Selection, Selection.End(xlDown)).Select

标签： excelvba

Answer 1

创建两个最后一行变量，去掉Select, ActiveCell，并使用Resize. 您可能需要使用 gRow - 1，因为您从 M2 等开始。这是一个示例...

Dim gRow As Long, hRow As Long

gRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 7).End(xlUp).Row
hRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 8).End(xlUp).Row

Range("M2").Resize(gRow).FormulaR1C1 = "=IFERROR(RC[-6]/'Sheet1'!R1C2,0)"  
Range("N2").Resize(hRow).FormulaR1C1 = "=IFERROR(RC[-6]/'Sheet1'!R1C2,0)"     
Range("O2").Resize(hRow).FormulaR1C1 = "=IFERROR(RC[-6]/'Sheet1'!R1C2,0)"