Excel Formula to add stock

[Pls_Help_noob]

Is it Possible to have have one target sell change one way only?

For example i want to use one cell only to add stock. What ever stock
arrives i just enter this number into the one cell and it changes. I have
the formula configured to change the stock held when stock in and stock out
change. But,


A B C D
1 PRODUCT NAME STOCK IN STOCK OUT STOCK HELD
2 Bananas 20 0 20
3 Apples 100 50 50
4 Pears
5
6 ADD STOCK ADD to STOCK IN
7 Bananas (enter number here)


I want to be able to enter the number of stock that i receive into one cell
so that all other cells change appropriately. For example if i get 20 more
bananas i want to enter 20 into cell "B7" so that this number will be added
to cell "B2". Thereafter if i change this number again when more stock comes
it adds again. Is this possible?

 

[Gary''s Student]

The following macro waits for changes in B7. After an entry is made, the
value will be added to the value currently in B2.

So enter 7 in B7 and B2 becomes 7.
Next enter 5 in B7 and B2 becomes 12.

Each time B7 is changed, B2 is updated:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r1 = Range("B7")
Set r2 = Range("B2")
If Intersect(r1, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = r2.Value + r1.Value
Application.EnableEvents = True
End Sub



Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Pls_Help_noob]

Great thanks a lot, two thumbs up! How can i rate your reply, I must be
blind because i cant see where to rate your response. Where can i find other
useful codes like this?

 

[Pls_Help_noob]

sorry ignore that last post. I followed those links which are very useful,
thanks. However i tried the code in an empty worksheet and it worked.
Thereafter i tried in my worksheet and it doesn't work. I tried again in
another worksheet and now it no longer works. I wonder if i have disabled
something by mistake haha! well wish me luck, I'm going to try and solve this
problem.

Cheers!

 

[Pls_Help_noob]

I wonder if you can help me again please. I got the macro working for one
pair of cells. However I want to apply the same model to a range of data. I
have tried the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r1 = Range("E8:E78")
Set r2 = Range("F8:F78")
If Intersect(r1, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = r2.Value + r1.Value
Application.EnableEvents = True
End Sub

After entering this macro i save the worksheet, reopen and try to enter the
data. When i enter the data i get a run-time error '13' and a message
asking me to debug. When i debug this row is highlighted "r2.Value =
r2.Value + r1.Value" I have opened the Help to try to solve this problem but
I can not resolve this issue.

Thanks for your time, I'm a complete Noob when it comes to this!

Regards Chris

 

[Gary''s Student]

First, I think you are doing a great job of picking up on the VBA. This
version is only one line different from you attempt:


Private Sub Worksheet_Change(ByVal Target As Range)
Set r1 = Range("E8:E78")
Set r2 = Range("F8:F78")
If Intersect(r1, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + Target.Value
Application.EnableEvents = True
End Sub

because any cell in column E can trigger the event, we use Target to tell us
which cell it was. We use OFFSET() because we just need to get to column F
for that row.

 

[Pls_Help_noob]

Hey Gary,

Thanks for your time and help, but unfortunately that code does not work for
me. I dont get any errors when using it, just nothing happens when i Insert
data.

Best regards,
Chris

 

[Pls_Help_noob]

Sorry Gary,

My mistake, I had to change the data range thats all. I forgot i added a
new column. My only excuse for being such a nimwit is lack of sleep and too
much reading. It's not in my genes....errr...honestly.

Well thanks for your help Gary. Time to call it a night for me. Have a
good day wherever you are.

Chris