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?