Insert New Line and One cell From Line Above Using VBA Micro Code

[enggirl712]

Hi Everyone,

I am trying to find a code that will allow me to insert a new line below a selected cell, merge the cells of the new inserted line and the cell above it for column B only, and copy the equation of column C into the new row line using a button. I have the button set up and a code to insert a new line but I can't get any further. If you could help me that would be wonderful.

Example:

Insert Line below ItemA1, Merge PriceB2 and empty cell below it and copy equation from D2 to D3

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]itemA1[/TD]
[TD]PriceB1[/TD]
[TD]UnitC1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]itemA1[/TD]
[TD]PriceB2[/TD]
[TD]UnityC2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NEW ROW (keep the same)[/TD]
[TD]NEW ROW (merge with above)[/TD]
[TD]NEW ROW (copy equation from above)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ItemA3[/TD]
[TD]PriceB3[/TD]
[TD]UnitC3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much in advance!

 

[Perpa]

Hi Everyone,

I am trying to find a code that will allow me to insert a new line below a selected cell, merge the cells of the new inserted line and the cell above it for column B only, and copy the equation of column C into the new row line using a button. I have the button set up and a code to insert a new line but I can't get any further. If you could help me that would be wonderful.

Example:

Insert Line below ItemA1, Merge PriceB2 and empty cell below it and copy equation from D2 to D3

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]itemA1[/TD]
[TD]PriceB1[/TD]
[TD]UnitC1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]itemA1[/TD]
[TD]PriceB2[/TD]
[TD]UnityC2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NEW ROW (keep the same)[/TD]
[TD]NEW ROW (merge with above)[/TD]
[TD]NEW ROW (copy equation from above)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ItemA3[/TD]
[TD]PriceB3[/TD]
[TD]UnitC3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much in advance!

enggirl712...welcome to the Forum.

If you select a cell in Column A, then press your button, the code below will do what you asked once loaded. You said you wanted to 'merge' the value in column B with the value in the cell above, but since there will be nothing in the new row in column B, I assumed you just wanted to copy the value from the cell above.
The formula in column C will adjust for changes, ie. if the formula in C2 is '=G2-H2, then the formula in C3 will be '=G3-H3'. If you do not wish the formula to change, then the formula in C2 should be in the format '=$G$2-$H$2'.
If the 'button' you have created is an ActiveX Command Button, then just click on the Developer tab and select 'Design Mode'. Then right click 'View Code' which opens the VB Editor and your code should be there. Delete everything between the first and last lines of code, then insert the code I have provided below except for the 1st and last lines thereof.
Close the Editor and save your workbook. It should already have been saved as 'macro enabled' when you saved your previous code.
Perpa

Sub InsertRowAndFormula()

    Selection.Offset(1, 0).EntireRow.Insert
    Selection.Offset(1, 0) = Selection
    Selection.Offset(1, 1) = Selection.Offset(0, 1)
    Selection.Offset(0, 2).Copy
    Selection.Offset(1, 2).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

For the formula in column C, it is unclear whether you want the identical formula copied down, or whether it should 'adjust' for any relative addressing.
I've chosen the identical formula approach - a guess due to the column B cells being merged.

Anyway, if neither Perpa's or my suggestions are exactly what you want, please provide more details. Make sure that the active cell is a cell in column A before running this code, though the code does check for this first.
Suggest that you test in a copy of your workbook.

Sub Insert_etc()
  With ActiveCell
    If .Column = 1 Then
      Rows(.Row + 1).Insert
      .Offset(1).Value = .Value
      .Offset(0, 1).Resize(2).MergeCells = True
      .Offset(1, 2).Formula = .Offset(0, 2).Formula
    End If
  End With
End Sub

 

[enggirl712]

Thank you Peter,

The code worked like a charm with a little bit of editing. I wanted to formula to copy the format and not the exact cell
I was wondering why you used and the if statement? I am a beginner to programming.

I am also trying to do one more thing:

I have a "To Date" column that I want updated whenever a new line is inserted. Basically it will add the quantities to day. I.e. add the quantity from the previous entry to the new one from the new row (which has not been entered yet). So far I have the code: .Offset(1, 6) = .Offset(0, 6) + .Offset(1, 5)
but its not working. Any ideas of how i can fix that?

Thank you

 

[Peter_SSs]

I was wondering why you used and the if statement?

That was to ensure that a cell had been selected in column A (not some other column) when the code was run because the code used the Active Cell as an 'anchor' for the Offsets. The code could be written to work with any cell selected, but that is the way I chose to do it at the time.

I am also trying to do one more thing:

I have a "To Date" column that I want updated whenever a new line is inserted. Basically it will add the quantities to day. I.e. add the quantity from the previous entry to the new one from the new row (which has not been entered yet).

Clearly you can't actually add a number that has not yet been entered. What you could do is enter a formula that would pick up that other number once it is entered (code below). However, that approach may have problems if yet another row ends up being inserted immediately above the one the initial code inserts.

Anyway, give this a try instead of that code snippet you posted.

.Offset(1, 6).FormulaR1C1 = "=R[-1]C+RC[-1]"

 

[enggirl712]

That is amazing! I am so impressed. How can I learn more about coding and how to do that on my own?
Thank you so much! I really appreciate it

 

[Peter_SSs]

You are welcome.

How can I learn more about coding and how to do that on my own?

Some people use books, some do courses, I've learned nearly all I know right here in the forum.