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?

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





--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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?

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?

"Gary''s Student" wrote:

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





--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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?

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!

"Gary''s Student" wrote:

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





--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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?

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" wrote:

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





--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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?

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.

--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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" wrote:

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





--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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?

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

"Gary''s Student" wrote:

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





--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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?

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

"Gary''s Student" wrote:

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.

--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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" wrote:

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





--
Gary''s Student - gsnu200758


"Pls_Help_noob" wrote:

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?