Macro or VBA to add a new column with values based on existing data

[mit-consulting]

Hi All,

I have a simple dataset, see columns A to D in attached file. This is sorted into Item No. and Date order ascending. I want to create a macro that adds a new column (E) called Forecast and then does the following...

1. For the first row for each change in Item No. is just copies the Inventory value (D) from that row to the new column

2. For each subsequent row for each Item No. it does the following calculation based on values from the previous row (column E - column C)

In the example file the result of cell E3 is E2-C2 and result of E4 is E3-C3 and so on and so on.

Any help would be very much appreciated.

Thanks in advance
Tim

[AlphaFrog]

Put this in E2 and copy it down the column

Please Login or Register  to view this content.

[mit-consulting]

Thanks so much, thats worked

[AlphaFrog]

You're welcome.

The formula doesn't consider the last quantity for each item number e.g. C12

I think you would want this formula to take into account the last quantity before a change in the Item No.

Please Login or Register  to view this content.

[ФдПщд]

Try that

Sub Subsequent()
Dim i As Long
Range("E2") = Range("D2")
For i = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 5) <> "" Then
Cells(i + 1, 5) = Cells(i, 5) - Cells(i, 3)
End If
Next i
End Sub

if something wrong inform me please.