A B C
D
Part number Qty Received Qty Shipped Balance

I have a spreadsheet with the above columns. I want to enter a qty. in
column B row* and it will add (update) the qty in coorespnding row cell in
column D. I also want to enter a value in a single cell in column c that will
subtract that value from all the cells in column D.

When entering a new value in cell C, the previous values entered shall be
retained in column D and will only be affected by the current entry. THANKS

I would do it like that:

Go to your workbook, rightclick on the tab of the targetworksheet --
View Code.
There you enter following code:
'-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Column
Case Is = 2
If IsNumeric(Target.Value) And Target.Value < "" Then
Target.Offset(0, 2).Value = Target.Offset(0, 2).Value +
Target.Value
Target.Value = ""
End If
Case Is = 3
If IsNumeric(Target.Value) And Target.Value < "" Then
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value -
Target.Value
Target.Value = ""
End If
End Select

End Sub
'-------------------------------------------------------------------------

I hope there won't be any wordwrap, but pay attention to that!

In my opinion you should clean the cell after updating Column D,
that's why i put in Target.Value = "" but you can delete that of
course.

hope i understood you correctly

Cheers Carlo

On Dec 20, 9:07 am, He cries for help
wrote:
A B C
D
Part number Qty Received Qty Shipped Balance

I have a spreadsheet with the above columns. I want to enter a qty. in
column B row* and it will add (update) the qty in coorespnding row cell in
column D. I also want to enter a value in a single cell in column c that will
subtract that value from all the cells in column D.

When entering a new value in cell C, the previous values entered shall be
retained in column D and will only be affected by the current entry. THANKS

Thanks Carlo, am I supposed to enter the target cells into the macro?

"carlo" wrote:

I would do it like that:

Go to your workbook, rightclick on the tab of the targetworksheet --
View Code.
There you enter following code:
'-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Column
Case Is = 2
If IsNumeric(Target.Value) And Target.Value < "" Then
Target.Offset(0, 2).Value = Target.Offset(0, 2).Value +
Target.Value
Target.Value = ""
End If
Case Is = 3
If IsNumeric(Target.Value) And Target.Value < "" Then
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value -
Target.Value
Target.Value = ""
End If
End Select

End Sub
'-------------------------------------------------------------------------

I hope there won't be any wordwrap, but pay attention to that!

In my opinion you should clean the cell after updating Column D,
that's why i put in Target.Value = "" but you can delete that of
course.

hope i understood you correctly

Cheers Carlo

On Dec 20, 9:07 am, He cries for help
wrote:
A B C
D
Part number Qty Received Qty Shipped Balance

I have a spreadsheet with the above columns. I want to enter a qty. in
column B row* and it will add (update) the qty in coorespnding row cell in
column D. I also want to enter a value in a single cell in column c that will
subtract that value from all the cells in column D.

When entering a new value in cell C, the previous values entered shall be
retained in column D and will only be affected by the current entry. THANKS

Hi

No, as this sub reacts everytime a cell is changed you don't have to
enter anything.
But to make it work you have to put it in the worksheet module of the
sheet you want to use it.

After the macro fires, it checks, if the changed cell is in column B
or C and adds or substract according to the column.

Pay attention, there was a wordwrap, the line after + and - should not
be wrapped!!!

You maybe need to add following rules:
Don't react if it is on the first row
and
Don't react if more than one cell is changed (because that would give
an error).

hth

Carlo

On Dec 20, 10:31 am, He cries for help
wrote:
Thanks Carlo, am I supposed to enter the target cells into the macro?



"carlo" wrote:
I would do it like that:

Go to your workbook, rightclick on the tab of the targetworksheet --
View Code.
There you enter following code:
'-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Column
Case Is = 2
If IsNumeric(Target.Value) And Target.Value < "" Then
Target.Offset(0, 2).Value = Target.Offset(0, 2).Value +
Target.Value
Target.Value = ""
End If
Case Is = 3
If IsNumeric(Target.Value) And Target.Value < "" Then
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value -
Target.Value
Target.Value = ""
End If
End Select

End Sub
'-------------------------------------------------------------------------

I hope there won't be any wordwrap, but pay attention to that!

In my opinion you should clean the cell after updating Column D,
that's why i put in Target.Value = "" but you can delete that of
course.

hope i understood you correctly

Cheers Carlo

On Dec 20, 9:07 am, He cries for help
wrote:
A B C
D
Part number Qty Received Qty Shipped Balance

I have a spreadsheet with the above columns. I want to enter a qty. in
column B row* and it will add (update) the qty in coorespnding row cell in
column D. I also want to enter a value in a single cell in column c that will
subtract that value from all the cells in column D.

When entering a new value in cell C, the previous values entered shall be
retained in column D and will only be affected by the current entry. THANKS- Hide quoted text -

- Show quoted text -