Adding or subtracting in a cell... clearer in post.

[hurshwhat]

My spreadsheet has 4 columns as follows:

1 |    A    |      B     |      C         |        D         
--------------------------------------------------------------------
2 | Item    | Quantity   |    Add         |   Subtract      
------------------------------------------------------------------
3 |    X1   |      21    |                |                     
----------------------------------------------------------------- 
4 |    Y2   |     54     |                |                    
-----------------------------------------------------------------
5 |    Z3   |      6     |                |

Is it possible to perhaps type in "1" into 3C and press enter, and make B3 automatically change into a "22", or type "3" into D4 and press enter, and make B4 automatically change into "51"? Any input would be much appreciated, thanks a lot!

 

[Smitty]

Welcome to the Board!

This should do what you want:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("C3:D500")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column
                <SPAN style="color:#00007F">Case</SPAN> 3
                    Target.Offset(, -1).Value = _
                    Target.Offset(, -1).Value + Target.Value
                <SPAN style="color:#00007F">Case</SPAN> 4
                    Target.Offset(, -2).Value = _
                    Target.Offset(, -2).Value - Target.Value
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

 

[erik.van.geit]

Hi, hurshwhat


try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
Dim operator As String
Dim CheckRange As Range
Dim C As Range
    
Const CC = 2        'Change Column

LR = Cells(Rows.Count, CC).End(xlUp).Row

Set CheckRange = Intersect(Target, Range("C2:D" & LR))

If CheckRange Is Nothing Then Exit Sub

Application.EnableEvents = False

    For Each C In CheckRange
        With C
            If .Value <> "" Then
                Select Case UCase(Cells(1, .Column))
                Case "ADD"
                Cells(.Row, CC) = Cells(.Row, CC) + C
                Case "SUBSTRACT"
                Cells(.Row, CC) = Cells(.Row, CC) - C
                End Select
            End If
        'if you want to empty the value enable next line
        '.Value = ""
        End With
    Next C

Application.EnableEvents = True

End Sub

this code will allow multiple changes at once (if needed)

kind regards,
Erik

EDIT: Hi, Smitty !
nice to se the different approaches

 

[Von Pookie]

Ah, shoot. Too slow. Pretty much the same thing as Smitty, but here's my version at anyway:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Qty As Variant

'if more than 1 cell changed, do nothing
If Target.Cells.Count > 1 Then Exit Sub

'if changed cell is not in column C or D, do nothing
If Intersect(Target, [C:D]) Is Nothing Then Exit Sub

Application.EnableEvents = False

'save the quantity value for the changed row to variable
Qty = Cells(Target.Row, "B").Value

'add or subtract according to what column was changed
Select Case Target.Column
    Case Is = 3 'column C --add value
        Qty = Qty + Target.Value
    Case Is = 4 'column D --subtract value
        Qty = Qty - Target.Value
End Select

'enter new value in quantity column
Cells(Target.Row, "B").Value = Qty

'remove the value that was entered in column C or D
Target.ClearContents

Application.EnableEvents = True

End Sub

Edit: One thing none of us seemed to mention, however: this is a worksheet event, so it needs to go in the correct module. Easiest way to get there is to right-click the tab of the sheet you want the code to work on and select 'View Code' from the right-click menu. The VBE should automatically open to the correct module and all you should have to do is paste the code in.

 

[hurshwhat]

Wow, thank you so much for the three different angles, not to mention the quick replies. I'm a little new to the VB aspect of Excel, so it'll be fun to analyze all three. I'll let ya know if I have any questions. Thanks again!


-Hursh

 

[erik.van.geit]

you're welcome !!

I assumed you may not enter values beyond the last row in column B and not in row 1

The select case will allow you to add more functions (although I think you will not need them)
tested also with "multiply" and "divide"
the order of the headers can be changed without needing to change the code
personnaly, I would put the "real sign" in the headers "-" or "+"

allowing for multiple entries allows you to paste values from another source (although I think - again - you will not need them)