Dynamic Subtraction

[Alpha-87]

Hello,

I have a problem that I just can't get my head around. It seems so simple, but it isn't...for me.

On (sheet 1) I have a form with drop down boxes and a button at the bottom. Pushing this button inputs all of that data into one row on (sheet 2). Every time that button is pressed, the row on (sheet 2) moves down and fills the new data above. This all works well.

However, on (Sheet 2) the cells in columns E and F are dates. I would like to know the difference in days between these dates and show that in column J. My problem lies in that no matter what formula I use, whenever a new data row is created, the number shown in column J remains in its cell, but refers to the row that has now moved down.

Basically, the button is pushed and a new row is entered in row 3. Column J subtracts the dates in column E from Column F ...this is fine.

A new row is entered by the VBA and fills row 3. The original row 3 has moved down and is now in row 4. However, the figure in Column J remains in row 3 but refers to row 4 (i.e. the row that used to be in row 3)

Is there a way to either move this cell down one, or have it refer to the same row each time and auto-fill the result? Simple things like $F$3-$E$3 don't work, they just change to $F$4-$E$4. Perhaps the VBA can be altered to fill a new row below instead of above.

The VBA I'm using is this;


Sub CopyInfo()
On Error GoTo Err_Execute

Sheet1.Range("G2").Copy
Sheet2.Range("A3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("G4").Copy
Sheet2.Range("B3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("I6").Copy
Sheet2.Range("C3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("I8").Copy
Sheet2.Range("D3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("I11").Copy
Sheet2.Range("E3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("I13").Copy
Sheet2.Range("F3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("I16").Copy
Sheet2.Range("G3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("I17").Copy
Sheet2.Range("H3").Rows("1:1").Insert Shift:=xlDown
Sheet1.Range("I18").Copy
Sheet2.Range("I3").Rows("1:1").Insert Shift:=xlDown

Err_Execute:
If Err.Number = 0 Then MsgBox "Data Recorded" Else _
MsgBox Err.Description
End Sub



Any help will be greatly appreciated.

[TMS]

Bit shorter:

Please Login or Register  to view this content.

[Alpha-87]

Thanks for the quick reply and the new VBA. However, the new problem is that the total sum in Column J now moves down with each new row and leaves a blank space above. Is there a way to force this to remain in place and calculate each new row?

Thanks again.

[TMS]

This will leave column J alone. Not sure what you mean about calculating each row.

Please Login or Register  to view this content.

[TMS]

Ah, think this is what you want:

Please Login or Register  to view this content.

[Alpha-87]

You are an actual genius. Thank you very much for this. That has solved my issue perfectly.

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.