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
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.
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.
Should this depend on the current date, or on the date in B1, B23, B44 etc.?
That is completely unrelated to the subject of this discussion. Please start a new discussion and ask your question there.
- Can you help me in my issue too sir? My issue is "Excel cannot open the file (file.xlsx) because the file format or file extension is not valid. Verify that the file has been not corrupted and that the file extension matches the format of the file"
Thank thank you very much, I really appreciate that you have help me with this issue.
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- Sorry the date is month/ day / year
- Yea sir I see what you mean, but what if my date in B1 changes? And i have to be in November. And then December?
See the attached version. I made the layout a bit more consistent.
- Yes, as you see there are 5 accounts and it has to happen on the current date
