excel addition tip

[abdulla saeed]

can anyone please help me in this task. i have tried but im sill unable to do so.

im using microsoft excel 2007

im using two cells. one cell is for the total and the other cell to enter the figure. what i want to do is delete the figure in the cell that i entered before and enter a new figure but to add this figure to the to the total figure and repeat this again and again. but when i tried to do this by using the sum function this did not work. when i use the sum formula, when i delete the figure in the figure cell the total also get deleted.


can anyone please help me with this through excel

[canapone]

Hi, if you need to add the new figure on the old number, you could write the new figure in a third cell and use copy and paste special choosing the option "add" on the old number.

Hope it helps you

[abdulla saeed]

its not what i meant. anyways thanks. but is there anyway the new number can be added to the old automatically as i add a new figure to the second cell. what goes wrong is that when i delete or replace the new figure from the second cell, it also deletes the previous figure from the total cell

[canapone]

Hi Abdulla,
as far as I know - but I'm a freshman compared to the experts of the Forum- you can not do it without a VBA routine.

I hope someone can offer a better answer than mine.

Regards

[DP978]

You are correct, itterations can only be done via VBA.

Once the value is changed int he original cell it is not stored for further use, nor is there any way to set order of cell operations.

[abdulla saeed]

can anyone please help me to do this task through VBA

[DP978]

How many times do you want this to repeat?

[abdulla saeed]

i want to do it idefinitely unlimited or its ok if its for about 500 times

[DP978]

Well if you do it indefinately you will end up with a broken workbook with unending code.

Does your value appraoch some other value and then stop? Or it just keeps going...?

[shg]

Private Sub Worksheet_Change(ByVal Target As Range)
    Const sAdrInp As String = "A1" ' input cell, change as desired
    Const sAdrOut As String = "B1" ' output cell, change as desired
    
    With Target
        If .Address(False, False) = sAdrInp Then
            If VarType(.Value) = vbDouble Then
                Me.Range(sAdrOut).Value = Me.Range(sAdrOut).Value + .Value
            End If
        End If
    End With
End Sub

Put the code in the appropriate Sheet module:

Adding Code to a Sheet module
1. Copy the code from the post
2. Right-click on the tab for the relevant sheet and select View Code. This opens the Visual Basic Editor (VBE) and shows the object module for the selected worksheet.
3. Paste the code in the window
4. Press Alt+Q to close the VBE and return to Excel

EDIT: That Roy is fast!

[royUK]

This code will work how you seem to want.

Enter numbers in A1 to increase/decrease B1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'will trigger when A1 changes
    If Target.Address <> "$A$1" Then Exit Sub
    Target.Offset(1, 0).Value = Target.Offset(1, 0).Value + Target.Value
End Sub

This code is Worksheet Event code, and will run automatically when something specific occurs in the sheet. If you type in a cell (A1), and press the Enter key, the worksheet has been changed. This will trigger the Worksheet_Change event and change B1.

Worksheet event code is stored in the worksheet module. To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste