Command Button - Insert Lines

I am attempting to write a command button to insert a line between two lines that already have formulas in them. I would like to insert the row and have the forumals shift down, but not be the same as above. Sort of something like below.


1 003 1st Floor CHWS&R 1st Floor 500.00 287.50 212.50
2 004 VAV Connections 1st Floor 25.00 125.00 -100.00

I want the line to insert between A and B, but I want B to move down to C and become 005, and I want the new line in B to be 004. I want that consistent for the codes in 3, 4, and 5. Thanks for your help.

Thanks,

TJB

I would need to see the formulas (not the results) in order to give a precise response.

As it is, all I can tell you is ... this will insert a row based on row number "n":

Rows(n).Insert Shift:=xlDown

The formulas will shift automatically if you have used relative row references throughout. If not, you will need to re-write the formulas for all rows below "n".

See if this is what you had in mind:

Private Sub CommandButton1_Click()
Dim currRow As Long
    ActiveCell.Activate
    currRow = ActiveCell.Row
    uChoose = MsgBox("Insert new row at " & currRow & "?", vbYesNo, "Please confirm ...")
    If uChoose = vbNo Then Exit Sub
    'insert new row
    Me.Rows(currRow).Insert
    'put formula in column A
    With Me.Range("A" & currRow)
        .FormulaR1C1 = "= R[-1]C+1"
        .NumberFormat = "000"
    End With
    'adjust formula for the old column
    Me.Range("A" & currRow + 1).FormulaR1C1 = "= R[-1]C+1"
End Sub

The button works (Thanks!), but the codes for E and F do not drop down. Also, I would like to allow undo. What code to I have to add so all formulas in the row that we are adding move down as well.

The A column has the formula:

A2 = A1+1

B column is user input

C Column is user input

D Column is user input

E Column has the formula:

E2 = 'Worsheet 2'!D482

F Column has the formula:

F2 = D2-E2

Thanks,

TJB

E Column has the formula: E2 = 'Worsheet 2'!D482
F Column has the formula: F2 = D2-E2

Let me be sure I understand ... for the NEW cell F2, you just want that it comes "pre-loaded" with the formula "=D2-E2". Is that correct?

Now, for E2 ... how do you know what cell it should apply to on another worksheet? (I'm sure you know, but I don't!)

Tell you what I can do ... I can put formulas there and then you can tweak if I don't get it right.

Private Sub CommandButton1_Click()
Dim currRow As Long
    ActiveCell.Activate
    currRow = ActiveCell.Row
    uChoose = MsgBox("Insert new row at " & currRow & "?", vbYesNo, "Please confirm ...")
    If uChoose = vbNo Then Exit Sub
    'insert new row
    Me.Rows(currRow).Insert
    'put formula in column A
    With Me.Range("A" & currRow)
        .FormulaR1C1 = "= R[-1]C+1"
        .NumberFormat = "000"
    End With
    'adjust formula for the old column A
    Me.Range("A" & currRow + 1).FormulaR1C1 = "= R[-1]C+1"

    'formula for column E
    With Me.Range("E" & currRow)
        .Formula = "= 'Worksheet 2'!D482"
    End With
    
    'formula for column F
    With Me.Range("F" & currRow)
        .FormulaR1C1 = "= RC[-2]-RC[-1]"
    End With
    
End Sub

Alright, as you suspected, the 'E' column is still a problem. The codes that show up in the "A" column of my spreadsheet are 1, 2, 3, 4, 5, 6, 7.......etc. In the other worksheet, the user uses these codes to log his time like so.



A B C D
Date Description Description Code Hours

1/29/2007 Logging Equipment 003 16

It then sums the hours by code at the bottom of the page, and the sum is reverted back to the front page in the "E" column. Does that make more sense? Thanks for all your help. It is greatly appreciated. I am working on it as well. If I come up with the code prior to you, I will let you know.

Thanks,

TJB