CODEWITHSUNDEEP
excelvba
Robert
Robert

Reputation: 69

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

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

Upvotes: 1

Views: 243

Answers (1)

GMalc
GMalc

Reputation: 2628

Create two last row variables, and get rid of Select, ActiveCell, and set your range using Resize. You will probably need to use gRow - 1 since you are starting at M2, etc. Here is an example...

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)"

Upvotes: 1

Related Questions