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
Difficult to help on developing a macro without the file on hand, but I have actually done something similar in the real world for a client. It involves a named range, a piece of code with "Currentregion" to count the number of rows that the formulae need to be copied to. Not very difficult, but depends a little bit the structure of your sheet.
Can you upload your file? Don't need the macro, just the structure. Will take the formulae from you original post.
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
