MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to add new value to values already in a cell


Posted by Veronica P. on July 18, 2000 10:59 AM

It seems simple but I can't solve it. In A1 I have =12+44+55 (values entered monthly). Instead to select manualy and type +the new value (55) I would like a macro to add from C1 the new value (55). Thanks a lot in advance.


Posted by Ryan on July 18, 0100 11:13 AM

Is the new value always added to C1?

Posted by Veronica P. on July 18, 0100 11:39 AM

Actualy I have more cells that need updated: A1:A10 and monthly I might add new ones and the new values are entered always in C1:C10 or Cn if new rows are inserted.

Posted by Veronica P. on July 18, 0100 11:58 AM

Ryan, can be something simple like I enter a value
in C1 run the macro and go inside A1 and add +new value to the values already in there (so will be addition of more values not total value, that was easy I did it even myselfe).I appreciate very much your help.

Posted by Ryan on July 18, 0100 12:54 PM

Veronica,

Here is a simple macro that will add the value in Column C to the cells in column A corresponding to the row that the number was entered into. The first macro here will ask if you want to update the cell( a sort of check), the second will automatically update it. I have it so if any number in C1:C1000 is entered it will update. If there is info in column C besides the numbers that you enter this will need to be changed. This code goes into a worksheet module for the worksheet that you use. To find the right spot here are the directions:

First, make sure the project explorer is open (ctrl-R). In here you will see different projects(workbooks) that are open. These are bolded. Find the workbook that you want this code in and unhide everything. There should be 2 folders. One that says Microsoft Excel Objects and another that says Modules (There is also one for forms if you have any). In the Excel Objects folder there is an object for each sheet in that workbook and a "ThisWorkbook" object. These are the modules that you want. So if you want this code to go into sheet1, double click on Sheet1 (Sheet1) and paste the code into there. That's all. Hope you find your way.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range

Set VRange = Range("C1:C1000")

Application.ScreenUpdating = False

If Union(Target, VRange).Address = VRange.Address Then
If MsgBox("Update cell " & Range("A" & Target.Row).Address(False, False) & _
"?", vbYesNo + vbQuestion, "Update") = vbYes Then
If Cells(Target.Row, 1).Formula = "" Then
Cells(Target.Row, 1).Formula = "=" & Target.Value
Else
Cells(Target.Row, 1).Formula = Cells(Target.Row, 1).Formula & "+" _
& Target.Value
End If
End If
End If

Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range

Set VRange = Range("C1:C1000")

Application.ScreenUpdating = False

If Union(Target, VRange).Address = VRange.Address Then

If Cells(Target.Row, 1).Formula = "" Then
Cells(Target.Row, 1).Formula = "=" & Target.Value
Else
Cells(Target.Row, 1).Formula = Cells(Target.Row, 1).Formula & "+" _
& Target.Value
End If
End If


Application.ScreenUpdating = True
End Sub

Posted by Veronica P. on July 18, 0100 1:54 PM

Thank you Ryan it's perfect.

Thank you for your help.