VBA - How to make formulas dynamic when inserting new rows?

[Jeofbist3]

Hello everyone,

I have a macro that insert new blank rows, then copy and paste some rows, based on a input userbox. Those last rows contain formulas but, once pasting them, they become incorrect (problem of references). I'd like to make the formulas dynamic in the macro code (via R1C1) or via Index function so that inserting news rows, results don't change. As you can see on the picture, some cells are refering to another sheet, some are in absolute / relative references, but at the end, only rows #19 and #20 contain formulas.

How can I adapt the code below?

Sub Stock(nbproduits As Long)
Dim MyN As String
Dim i As Long, MyMarker As Long, MyM As Long, LstRW As Long
Dim ws As Worksheet: Set ws = Stocks
If nbproduits = 0 Then
MyN = InputBox("How many products do you want to add?", "My Input Box")
 If Not IsNumeric(MyN) Then 
MsgBox "Entrez un nombre svp", vbCritical, "Error"
            Exit Sub
        End If

        MyN = CInt(MyN)
    Else
        MyN = CInt(nbproduits)
    End If
For MyMarker = 1 To 1

  LstRW = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
MyM = Application.Match("Marker" & MyMarker, ws.Range(ws.Cells(1, 1), ws.Cells(LstRW, 1)), 0)

            For i = 1 To MyN

                ws.Rows(MyM + 1 & ":" & MyM + 6).Copy

                ws.Rows(MyM + 1 + 6 * i).EntireRow.Insert Shift:=xlUp

                ws.Rows(MyM + 1 + 6 * i).PasteSpecial Paste:=xlPasteFormats

                'ws.Cells(MyM + 1 + 6 * i, 4).FormulaR1C1 = ... this formula should be : cell D26 * sheet 2.Données prévisonnelles!$F62 for formulas on row #19
                'ws.Cells(MyM + 1 + 6 * i, 5).FormulaR1C1 = this formula should be : cell E26 * sheet 2.Données prévisionnelles!$F62 for formulas on row #19
                ' etc... until column BP for formulas on row #19
                 'ws.Cells(MyM + 1 + 7 * i, 4).FormulaR1C1 = ... this formula should be : =+IF('2. Données prévisionnelles'!C$16>0;'2. Données prévisionnelles'!$C62;0) for formulas on row #20
                  'ws.Cells(MyM + 1 + 7 * i, 5).FormulaR1C1 = ... this formula should be : =+IF('2. Données prévisionnelles'!C$16>0;'2. Données prévisionnelles'!$C62;0) for formulas on row #20, etc.. until column BP for formulas on row #20


            Next i
    Next MyMarker

End Sub

I've posted this thread on VBA - How to make formulas dynamic when inserting new rows? - OzGrid Free Excel/VBA Help Forum but no reliable answer so far.

Thanks a lot !

 

[Fluff]

Please supply ALL cross post links, not just one of them. Thanks

 

[Jeofbist3]

Hi, thanks. Here it is: VBA - How to make formulas dynamic when inserting new rows? and VBA - How to make formulas dynamic when inserting new rows?

 

[Fluff]

Thanks