Forum Discussion
How to autofill multiple formulas with a range that is down to the last cell of a certain column?
This piece of code will enter the formulae in AC, AG:AI and AL and copy them down until the last row. Note I changed the formula in AL as the one you provided (=(ABS)(RC5-RC36)) didn't seem to be correct.
Sub CopyFormulae() Dim EndRow As Integer EndRow = Sheets("Trade Log").Range("A1").CurrentRegion.Rows.Count Range("AC4").FormulaR1C1 = "=LOOKUP(RC28,Table1)" Range("AC4").Select Selection.Copy Range("AC5" & ":AC" & EndRow).Select ActiveSheet.Paste Range("AG4").FormulaR1C1 = "=WEEKNUM(RC27)" Range("AH4").FormulaR1C1 = "=WEEKDAY(RC27)" Range("AI4").FormulaR1C1 = "=IF(RC10>0,""W"",IF(0>RC10,""L"",""F""))" Range("AG4:AI4").Select Selection.Copy Range("AG5" & ":AI" & EndRow).Select ActiveSheet.Paste Range("AL4").FormulaR1C1 = "=ABS(RC5-RC36)" Range("AL4").Select Selection.Copy Range("AL5" & ":AL" & EndRow).Select ActiveSheet.Paste Application.CutCopyMode = False End Sub
Riny_van_Eekelen Hi Riny - thank you so much for replying! The file is attached. The sheet in question is the "Trade Log" sheet. I'll need to do the same thing for the text to columns stuff in there as well.
Naan_Deal Almost diner time in my part of the world. Give me some time. Will get back to, if not someone else does.
Riny_van_Eekelen No problem at all, thank you very much again for taking a look at it
This piece of code will enter the formulae in AC, AG:AI and AL and copy them down until the last row. Note I changed the formula in AL as the one you provided (=(ABS)(RC5-RC36)) didn't seem to be correct.
Sub CopyFormulae() Dim EndRow As Integer EndRow = Sheets("Trade Log").Range("A1").CurrentRegion.Rows.Count Range("AC4").FormulaR1C1 = "=LOOKUP(RC28,Table1)" Range("AC4").Select Selection.Copy Range("AC5" & ":AC" & EndRow).Select ActiveSheet.Paste Range("AG4").FormulaR1C1 = "=WEEKNUM(RC27)" Range("AH4").FormulaR1C1 = "=WEEKDAY(RC27)" Range("AI4").FormulaR1C1 = "=IF(RC10>0,""W"",IF(0>RC10,""L"",""F""))" Range("AG4:AI4").Select Selection.Copy Range("AG5" & ":AI" & EndRow).Select ActiveSheet.Paste Range("AL4").FormulaR1C1 = "=ABS(RC5-RC36)" Range("AL4").Select Selection.Copy Range("AL5" & ":AL" & EndRow).Select ActiveSheet.Paste Application.CutCopyMode = False End SubRiny_van_Eekelen I cannot thank you enough Riny. I'm really really grateful. Thank you so much!