Insert formula every nth Row?

[Retail_Therapy]

From Columns AN to BB (15 columns in total), I have a match/index formula that needs to be inserted every 11th row.
Does anyone know of a VBA code to perform this?

Note: each of the 15 column is a unique match/index formula catered to that specific column cell.

Thank you in advance,
Kim

 

[Joe4]

Maybe something like this:

Sub MyInsert()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Set initial row to insert first formula on
    r = 11
    
'   Loop until column AN is blank
    Do Until Cells(r, "AN") = ""
'       Insert blank row
        Rows(r).Insert
'       Insert formulas
        Cells(r, "AN").Formula = "=ROW()"
        Cells(r, "BB").Formula = "=COLUMN()"
'       Add 11 to row counter
        r = r + 11
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

Obviously, you will want to replace the formulas I used with whatever formulas you want.

 

[Special-K99]

What are the formulas you want in AN11, AO11 and AN22 and AO22 ?

 

[Retail_Therapy]

first row of formulas is in row #5
so AN5, AO5, AP5, AQ5, AR5, AS5... ETC

thank you very much!!!

 

[Joe4]

first row of formulas is in row #5
so AN5, AO5, AP5, AQ5, AR5, AS5... ETC

Yep, then just initially set r to 5,

'   Set initial row to insert first formula on
    r = 5

 

[Retail_Therapy]

do I enter the forumla in between the quotation mark below:

Do Until Cells(r, "AN") = ""

thank you for your prompt assistance!

 

[Joe4]

do I enter the forumla in between the quotation mark below:

Do Until Cells(r, "AN") = ""

No. That is just telling it to stop when there is no longer any data in column AN.
We may need to make a slight adjustment to that if it is not picking up your last section of data. Just let me know if that is the case.

 

[Retail_Therapy]

yes unfortunately nothing happens when I run it ....

 

[Joe4]

yes unfortunately nothing happens when I run it ....

That tells me that you probably do not have any data at all in column AN. Is that the case?

Let's approach this part a little differently.
Can you tell us how we can determine how far down we should go in inserting formulas?
Is it a set number of times? Up to a certain row? Or based on data somewhere on the sheet?

 

[Retail_Therapy]

That tells me that you probably do not have any data at all in column AN. Is that the case? there's no data -only a formula in starting from AN5 through BB5. the formula is a match/index derived from 2 worksheets.

Let's approach this part a little differently.
Can you tell us how we can determine how far down we should go in inserting formulas? from AN5:BB5 all the way to row #2942 -formula needs to be inserted every 11th row only. right now I'm just copying/pasting the formulas every 11th row but with this amount data it is very repetitive.