Adding numbers in a cell

the_deejus

New Member
Joined
May 15, 2006
Messages
36
Hi all,
A friend at work says that there is a way to add numbers in a cell, simply by over typing it.

For example: Say we have the number 3 in a cell, if one moves the cursor to that cell and types in a number 4, then excel automatically adds the two numbers together and shows the number 7...is this possible? if so what is the formula?

Thanks,
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
AFAIK, the way you are telling is not possible or having incorrect information.

Whereas, the possible way is... say if you hv number 3 already in cell and you have copied any number (say 4) you can paste in same cell by right clicking and paste-special - Paste - Values and Operation - Add and ok.

And see the result now. HTH.

ilyaskazi
 
Upvote 0
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, x, y
Set cell = Intersect(Target, [A1])
If cell Is Nothing Then Exit Sub
With Application
    .EnableEvents = False
    x = cell
    .Undo
    y = cell
    On Error Resume Next
    cell = x + y
    On Error GoTo 0
    .EnableEvents = True
End With
End Sub
 
Upvote 0
Here's another possibility. This will still allow normal entry in the cell, including deleting the value, typing text in the cell, etc. but if the last value was a number and the new value is a number it will add them together.

Code:
Public lastvalue

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Target = "" Then Exit Sub
If IsNumeric(Target) And IsNumeric(lastvalue) Then
    Application.EnableEvents = False
        Target = Target + lastvalue
    Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then lastvalue = Target
End Sub
 
Upvote 0
HOTPEPPER

What about if a user drags a number into A1, or copies and pastes numbers to A1:A5(say) ?
Also, on opening the workbook, if A1 is the active cell and a number is input, it will not be added to the existing number because the "lastvalue" will not have been set (I suppose this could be overcome by having a WorkbookOpen procedure to select a cell other than A1).

Amending the code I posted previously to allow for input of non-numeric values and clearing contents :-

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, x, y
Set cell = Intersect(Target, [A1])
If cell Is Nothing Then Exit Sub
With Application
    .EnableEvents = False
    If Target.Address <> "$A$1" Then
        MsgBox "Change A1 with a single input only."
        .Undo
        GoTo e
    End If
    x = cell
    If Not IsNumeric(x) Or x = "" Then GoTo e
    .Undo
    y = cell
    If IsNumeric(y) Then
        cell = x + y
    Else
        cell = x
    End If
e:    .EnableEvents = True
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,216,088
Messages
6,128,744
Members
449,466
Latest member
Peter Juhnke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top