Forum Discussion
How to autofill multiple formulas with a range that is down to the last cell of a certain column?
Hi there,
I'm trying to run 5 basic formulas in one macro:
ActiveCell.FormulaR1C1 = "=LOOKUP(RC28,Table1)"
ActiveCell.FormulaR1C1 = "=WEEKNUM(RC27)"
ActiveCell.FormulaR1C1 = "=WEEKDAY(RC27)"
ActiveCell.FormulaR1C1 = "=IF(RC10>0,""W"",IF(0>RC10,""L"",""F""))"
ActiveCell.Formula2R1C1 = "=(ABS)(RC5-RC36)"
These formulas all run in different columns. I need them to fill the formula down in their respective columns, from the start of NEW data in column A (starting at A4) to the end of NEW data in column A.
So basically the formula shouldn't run in cells in its column that are already filled, with the end of thedata in column A being the cut off.
Thanks very much in advance! Have been struggling a lot with this 😞
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
7 Replies
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.
