Below is the excel sheet
A B C D E F G H I
1 Product Number Stock In Stock Out Held Pro. No. Add to Stock In Add to Stock Out
2 20487 20487
3 61400 61400
4 61200 61200
5 7007 7007
6 70523 70523
7 7005 7005
The below code works fine for Stock In, and will add to Col B from user entry in Col H but I need it to do this for all the rows for each product number & also the same for Stock Out.
The held column just deducts the cell in Col C from the cell in Col B, i.e.forumla in Col D Row 2 is =B2-C2.
I know I need to use Range as Range("H2:H7") to incorporate all cells required in that column; however I am not sure of the code that should go in instead of " In2.Value = In2.Value + In1.Value" I have tried
"Target.Offset(0, -6).Value = Target.Offset(0, -6).Value + Target.Value", nothing happens can't figure it out nor can I figure out how to have it do the same for stock out. Any help is appreciated.
- Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Set In1 = Range("H2")
Set In2 = Range("B2")
If Intersect(In1, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
In2.Value = In2.Value + In1.Value
Application.EnableEvents = True
End Sub