VBA to auto insert formulas

[happyhungarian]

Hi, I'm using the following code to auto insert rows and data labels in Column D for me. Can this be altered to insert a simple formula? Basically what I would like is to keep the code for column D as is {entering Customer1, Customer2, and Cusomer3} but in columns A through C I'd also like it to enter a formula that simply references the cell directly above. Example. My headers are in Row 1, the VBA skips row 2 and auto-inserts 3 rows and places "Customer1" in cell D3, "Customer2" in cell D4, "Customer3" in cell D5. Now what i want to add is in cell A3 i want it to reference A2, cell B3 to reference B2, cell C3 to reference C2. Then the same for row 4, A4 reference A3, B4 reference B3 etc. I hope I made sense!

Dim lr As Long, fr As Long, i As Long
Dim MyCol As String, MyVals As Variant
MyCol = "D" 'Adjust to the appropriate column containing the list
MyVals = Array("Customer1", "Customer2", "Customer3")
lr = Cells(Rows.Count, MyCol).End(xlUp).Row + 1
fr = Columns(MyCol).Find("CCC").Row + 2 'Adjust the name of the header
For i = lr To fr Step -1
Rows(i).Resize(3).EntireRow.Insert
Cells(i, MyCol).Resize(3, 1).Value = Application.Transpose(MyVals)

 

[texasalynn]

do you want to just copy the formula and paste onto the new row?

 

[happyhungarian]

Well there isnt really a formula to copy... I'd want it to insert the formula that references the cell above. In my example I mention that I would like cell A3 to reference A2 But in A2 there would be text so I would want A3 (since that's one of the inserted rows) to reference A2 which is the existing line. So essentially it would be as followed:

A2-existing text
A3-inserted row with formula referencing A2
A4-inserted row with formula referencing A3
A5-inserted row with formula referencing A4
A6-existing text
A7-inserted row with formula referencing A6
etc.

Thanks

 

[texasalynn]

this will do that
Cells(i,1).FormulaR1C1 = "=+RC[-1]"