How to autofill multiple formulas with a range that is down to the last cell of a certain column?

[Naan_Deal]

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 :(

[#2]

@Naan_Deal 

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.

[#3]

@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. 

[#4]

@Naan_Deal Almost diner time in my part of the world. Give me some time. Will get back to, if not someone else does.

[#5]

@Riny_van_Eekelen No problem at all, thank you very much again for taking a look at it

[#6 · Accepted solution]

@Naan_Deal 

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]

@Riny_van_Eekelen I cannot thank you enough Riny. I'm really really grateful. Thank you so much!

[#8]

@Naan_Deal Most welcome!