Forum Discussion
Solved
excel
Hello all, hope any of you can help me with a small issue that i have. I have cell A1 and A2 cell A1 has to add up numbers from cell A2 but cell A2 changes everyday and i want cell A1 to keep adding...
You mentioned "it has to happen on the current date" so the code uses the current date. If you want to use the date in B1 instead, change the code to
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Range("B3,B20,B37,B54,B71"), Target) Is Nothing Then If IsNumeric(Target.Value) Then Application.EnableEvents = False Set rng = Target.Offset(0, 2).Resize(12, 1).Find(What:=Format(Range("B1").Value, "mmmm"), LookAt:=xlWhole) rng.Offset(0, 1).Value = Target.Value + rng.Offset(0, 1).Value Application.EnableEvents = True End If End If End Sub
Hello sir, thank you for you're reply and answer, but in the spreadsheet i dont want to show all the rows with numbers because it is on a daily base. Prefer to have a formula to keep adding cell A1
You cannot do that with a formula. It requires VBA.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the code window.
Close the Visual Basic Editor.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2"), Target) Is Nothing Then
Application.EnableEvents = False
Range("A1").Value = [=SUM(A1:A2)]
Application.EnableEvents = True
End If
End SubCan you explain in detail what you want to do?
- Ok let me try to explain, as you see the spreadsheet in colom B4 has a value that is going to change everyday, that value needs to be added in colom E13 as you see i am doing it on a monthly basis means that for every month at the end of the month i will have a value that sums up every days value.
If there was a possibility for me to get that so when the month changes it would go there also. for example we are in the month of October get the daily value for the month Oct. as we switch to November to get it in the November month.
- Hello sir, can I send you the spreadsheet that i am working on so you have a understanding what i mean. Because it is not working for me.
- Thank you very much.