CODEWITHSUNDEEP
vbaexcel
Mantas La
Mantas La

Reputation: 49

Excel how to make auto SUM form

I want, then I enter a number (value) in the cell it auto sum(or it's better after clicking "enter" or clicking the button), and the result display in another cell. After that the cell became empty and I can enter next value in the same cell. For example. I have two cells A2 and B2. In the first, I always enter data, in the second - shows totals. So if we got in cell B2 the result "230". then I enter in the cell A2 100 after press ENTER or some button the result in B2 become "330" and A2 after that become empty. And this I can do so many time as I want.

Please help me with the code. Thanks in advance.

Upvotes: 1

Views: 272

Answers (2)

Ramandeep Singh
Ramandeep Singh

Reputation: 56

PFB for the required code as per your requirement. This macro will sum the value in cell A2 and B2 and give output in cell B2.

Sub AutoSumming()

'Declaring variables
Dim Variable1, Total As Long

'Initializing the variables
Variable1 = Range("A2").Value
Total = Range("B2").Value

'Performing the sum
Total = Total + Variable1

'Reassign values back to cells
Range("B2").Value = Total
Range("A2").Value = ""

End Sub

Upvotes: 1

Joshua Hysong
Joshua Hysong

Reputation: 1072

You'll need a macro that triggers when a cell value changes. The following code is triggered on a worksheet change event and then checks if the cell changed is A2 and not empty. If so then add the value of A2 to the current value of B2 and set A2 back to null.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("A2")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
        And Range("A2") <> "" Then

        Range("B2").Value = Range("B2").Value + Range("A2").Value
        Range("A2").Value = ""
        Range("A2").Select

    End If
End Sub

Add this as a macro to your current worksheet and then any numbers you enter into A2 will be added to the current value of B2.

This is just quick example code so you will likely need to modify to suit your specific need. I would research validating entries are numbers and not text also.

Upvotes: 1

Related Questions