Auto fill specific cell range with formulas-CodePudding

I have this formula:

IF(ROWS($Q$27:Q27)<=$P$25,INDEX(DataTable[[#All],[Time]],$P27),"")

and if I drag it to the right, it should automatically read each column respectively; example:

=IF(ROWS($Q$27:R27)<=$P$25,INDEX(DataTable[[#All],[Name]],$P27),"")

^Notice that the first Q27 is fixed, the second Q27 is variable.

I drag this formula to the right by 15 columns, and down to 50 rows. that's 750 formulas in total.

I want to do this in vba, but if I did this, it will be 750 lines of code for each cell representing each row/column.

example: .Range("G17").Formula=IF(ROWS($Q$27:R27)<=$P$25,INDEX(DataTable[[#All],[Name]],$P27),"""")

and if I drag it down, it will automatically pick up what I exactly want, example:

=IF(ROWS($Q$27:Q28)<=$P$25,INDEX(DataTable[[#All],[Time]],$P28),"")

so this formula should be written 750 times in total for the cell range [ A27:N76 ]

Any faster / more dynamic approach? and if possible, can I make it depend on more than 50 lines based on a cell value inside the sheet?

Example:

CodePudding user response：

so this formula should be written 750 times in total for the cell range [A27:N76]

You don't need to do that. If you specify range.Formula, it will fill the proper formulas all the way across and down. Just give it the formula of the top/left most cell.
So, in your case

Range("A27:N76").Formula = "=IF(ROWS($Q$27:R27)<=$P$25 ... "

CodePudding user response：

This should do it all in one line:

 Range("A27:N76").FormulaR1C1 = "=IF(ROWS(R27C17:RC[16])<=R25C16,INDEX((DataTable[[#All],[Name]],RC16),"""")"