1

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
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Robert
  • 69
  • 5
  • 2
    1) [Better to not `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and 2) You don't need to `FillDown`. Just write the formula to the entire range in one step. – BigBen Feb 13 '20 at 16:02
  • As long as the `ActiveSheet` is the correct one, `ActiveSheet.Cells(ActiveSheet.Rows.Count, 8).End(xlUp).Row` should give you the last row. Are you working with a table perhaps (the one you would create with Ctrl+T)? – BigBen Feb 13 '20 at 16:05
  • You need to set different last row variables. e.g. lRow1, lRow2, etc. – GMalc Feb 13 '20 at 16:55

1 Answers1

1

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)"
GMalc
  • 2,608
  • 1
  • 9
  • 16
  • Tbh, I'd prefer `Range("M2:M" & gRow)` over `Resize` here. If `gRow` = 23, then `Range("M2").Resize(gRow)` refers to `M2:M24`, not `M2:M23` (which now that I reread your question, I see you were hinting at already). No need to use `gRow - 1`, just concatenate `gRow` into the range address. – BigBen Feb 13 '20 at 17:14
  • @BigBen: I like using `Resize` – GMalc Feb 13 '20 at 17:23
  • Eh... bad practice IMO. – BigBen Feb 13 '20 at 17:24
  • @BigBen: I respect your opinion. But, I've seen too many try to concate the lastrow into the range and not do it correct, e.g. see the OPs code. – GMalc Feb 13 '20 at 17:25
  • Thanks @GMalc , your code worked better for me.Would you know how I can also force user to insert currency conversion rate when opening file and use the entered value for calculation instead of cell reference? – Robert Feb 14 '20 at 14:37
  • @Robert: you could try to write the code and then if you need assistance ask another good question. – GMalc Feb 14 '20 at 15:20