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

jeoffrey3 · Apr 8th 2020

I have a macro that copy and paste rows, based on a input userbox. Those rows contain formulas but, once pasting them, they become incorrect (problem of references). I'd like to make the formulas dynamic in the 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?

I've created a thread on this website as well: https://stackoverflow.com/ques…serting-new-rows#61101725

Thanks !!

Code

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 = "R[12]C* RC[-1])" testing
'ws.Cells(MyM + 1 + 6 * i, 4).FormulaR1C1 = "RC[12]* RC[-1])" testing
Next i Next MyMarker
End Sub

Display More

**royUK** · Apr 8th 2020

The best way is to convert the data to Table, then formulas will self populate as rows or added.

Overview of Excel Tables

I replied to your previous post about the layout of your worksheets - they are just making work for you. Where is the data from?

jeoffrey3 · Apr 8th 2020

Hi Roy,

A table could work if I know in advance the number of rows that will be added. I've tried to create a table between row #18 and #23, but when running the macro, I have an error message. Besides, I'd like to keep the automation if possible.

How to do it properly then?

Thanks!

**royUK** · Apr 8th 2020

Where is the data coming from?

Basically you stocks sheet is a summary sheet and should be linked to the data.

jeoffrey3 · Apr 8th 2020

Hi Roy, thanks for your reply.

Data in sheet 12.Stocks come from sheet 2.Données prévisionnelles (those date are entered manually by the user).

**royUK** · Apr 9th 2020

Link to cross post provided

https://www.mrexcel.com/board/…cel-sheet-by-vba.1129661/

**AliGW** · Apr 10th 2020

Also cross-posted at: excelforum.com/excel-programmi…n-inserting-new-rows.html

Our rules require YOU to declare ALL cross-posting.

**royUK** · Apr 10th 2020

For MyMarker = 1 To 1 does not make sense the code will only ever find Marker1.

In my opinion not only is the workbook design poor the code is also not good.

I would actually instruct the user to select a cell where the rows are to be added

Uour code when I got it to work copies a block of text and pastes it below, why?

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

Share

Tags