Automatcially calculate and clear cell

[JOSTERBAUER]

I am a newbie at this, how can I enter a qty into say cell b1, and have it add or subtract that qty to a1, and clear the contents of b1? Thanks in advance

 

[Peter_SSs]

Try this. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Test

<font face=Courier New><br><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)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("B1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(Range("B1").Value) <SPAN style="color:#00007F">Then</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            Range("A1").Value = Range("A1").Value + Range("B1").Value<br>            Range("B1").ClearContents<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Mr_Roscoe]

JB - this VB code can go into the sheet where you require this calc:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A1_Value As Long, B1_Value As Long, New_Value As Long

If Target.Cells.Address = "$B$1" Then
Application.EnableEvents = False
A1_Value = Range("A1").Value
B1_Value = Range("B1").Value
New_Value = A1_Value - B1_Value
Target.Offset(0, -1).Value = New_Value
Range("B1").Value = 0
Range("B1").Select
Application.EnableEvents = True
End If
End Sub

cheers, Ian R.

 

[Peter_SSs]

Ian, a couple of comments on your code. Note that it will error if (presumably accidentally) a non-numeric value is entered in B1. Also if a fractional value (eg 3.4) is enetered in B1, your code will produce unexpected results due to your use of 'Long' variable types.

 

[Mr_Roscoe]

Peter - as soon as i saw you had posted i thought my code would never be as good.

I like the Isnumeric check as mine will error if the B1 value is not numeric.

Removing the dim as long enabled a fractional number.

Thx for the tips, Ian R.

 

[Peter_SSs]

Removing the dim as long enabled a fractional number.

I think you should always declare your variables. Instead of removing the Dim statement you could have declared the variables as 'Single' (or 'Double') instead of 'Long'.

I also suggest that you use Code tags when posting code so that indentations are preserved. It is much harder to read (& debug) code that is all just left-aligned.

 

[JOSTERBAUER]

That code works. How can you modify the code to do that on a multiple cell level? Or do you have to copy, past and change values in that code? Ex - have that code work for a1 to b1, a2 to b2, ect.

 

[Peter_SSs]

That code works. How can you modify the code to do that on a multiple cell level? Or do you have to copy, past and change values in that code? Ex - have that code work for a1 to b1, a2 to b2, ect.

Depending on whether you have a fixed range in column B to check or the whole column, choose the appropriate line near the start of this code. The code would allow multiple cell changes at once (eg Copy a range of cells and paste them into the target range, or select several cells in the column B range and enter a number and confirm with Ctrl+Enter, not just Enter.)

<font face=Courier New><br><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)<br>    <SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> CheckCells <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B1:B10" <SPAN style="color:#007F00">'<- Fixed range</SPAN><br><SPAN style="color:#007F00">'    Const CheckCells As String = "B:B"   '<- Whole column</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range(CheckCells))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Changed.Cells<br>            <SPAN style="color:#00007F">If</SPAN> IsNumeric(c.Value) <SPAN style="color:#00007F">Then</SPAN><br>                c.Offset(, -1).Value = c.Offset(, -1).Value + c.Value<br>                c.ClearContents<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[JOSTERBAUER]

That works great. That is exactly what I was looking for. Thanks for the help