add to existing value

[fatdog]

I need to allow people to enter values that are then added to a single cell to maintain a running total, but i want to keep it as simple as possible for the user so they don't need to add values manually to avoid potential user error

a simplified example, if 10 was in a cell already, one person may need to submit a value of 4, another user 6, and at the end of the day i would expect to see a single cell containing the number 20

if a1 contains a value, is it possible to have it setup so someone can enter a value into b1 then that value will be added to the number currently in a1 and then b1 is then cleared ready for the next person

or alternatively could someone enter a value into cell a1 directly and it be added to the current value rather than clearing and replacing whats already in there

any tips would be greatly appreciated

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B1" Then
    Application.EnableEvents = False
    Range("A1").Value = Range("A1").Value + Target.Value
    Target.ClearContents
    Application.EnableEvents = True
End If
End Sub

then try entering numbers in B1.

 

[fatdog]

thanks this seems to work nicely, is there any way to adapt this to run down the column, excluding the heading

so this system runs in cells a2 / b2 and below?

 

[Peter_SSs]

Not sure which way round you wanted it to work. This looks for changes in column B and adds the value(s) to the corresponding cells in column A. Should work even if multiple cells in col B are changed at once.

<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> Bchanged <SPAN style="color:#00007F">As</SPAN> Range, Bcell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Bchanged = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Bchanged <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> Bcell <SPAN style="color:#00007F">In</SPAN> Bchanged<br>            <SPAN style="color:#00007F">If</SPAN> IsNumeric(Bcell.Value) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> Bcell.Offset(, -1)<br>                    .Value = .Value + Bcell.Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> Bcell<br>        Bchanged.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">Sub</SPAN><br></FONT>

 

[fatdog]

thank you, this is working nicely

 

[Luigi802]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B1" Then
    Application.EnableEvents = False
    Range("A1").Value = Range("A1").Value + Target.Value
    Target.ClearContents
    Application.EnableEvents = True
End If
End Sub

then try entering numbers in B1.

I have the same issue, except I want to do this with multiple cells in a table, this works beautifully in a single line, but how can I do this ove a range in a table?

 

[Peter_SSs]

I have the same issue, except I want to do this with multiple cells in a table, this works beautifully in a single line, but how can I do this ove a range in a table?

Can you spell out your specific circumstances a little more?

 

[Luigi802]

Sure instead of doing it on just A1 using B1 I want to do it on A1:A25 using corresponding B1:B25 also they are in a table together, I tried duplicating your macro, but I'm not too good with those lol

 

[Peter_SSs]

.. I want to do it on A1:A25 using corresponding B1:B25 also they are in a table together, ..

If they are in a table, wouldn't row 1 be headings and not numbers?

 

[Luigi802]

Yes but I just used that as an example, the rows I'm dealing are actually I14:I 25 and K14:K25, sorry didn't think that was relevant