how to auto update sum?
 

Hi all

I have to run SUM from the last day of the month to A5 for column A and
B5 for coulmn B(I have 2 column in which I type data) The code I have
here does the job, but only if you actually open the sheet at type
somthing that day. If I don't type in anything that day, then it does
not opdate and I would have skipped a month :(

any help appreciated

This is part of the code:


---------------------------------------------------------------------------*------------------------------------------



Jan:


With Target


If .Column < 1 Then GoTo Jan2
If DateNow < DateJan Then GoTo Feb


Application.EnableEvents = False
Range("I6").Value = Application.Sum(Range(Target, Range("A5")))



Application.EnableEvents = True


End With


Jan2:


With Target


If .Column < 2 Then GoTo InsertDate
If DateNow < DateJan Then GoTo Feb2


Application.EnableEvents = False
Range("J6").Value = Application.Sum(Range(Target, Range("B5")))

Application.EnableEvents = True


End With


---------------------------------------------------------------------------*------------------------------------------



- Column 1 and 2 are where I type in the amount of units I make.
- "I6" and "J6" are were they are stored.
- "InsertDate" is a third column were the date is.
- "DateJan" is the last date of the month where the data is sumed and
stored in the fields "I6" and "J6" and voila the graph apprears.
- "B5" is the first data field


But the big problem is that this only happens IF I type in anything
that particular date! If I don't make anything or it is a weekend then
im screwed! The graph just won't get updated...then the next month I
get the total number of units made for 2 month and not just 1 because
it didn't update the last month.


help thanks


San


Besvar »

how to auto update sum?
 

If you don't update it, then what value is it supposed to assume? Seems like
you need to update it for each period.

Or you need to form a clearer explanation. If it was clear to others, I am
sure you would have a better answer than I am providing.

--
Regards,
Tom Ogilvy


"San" wrote:


Hi all

I have to run SUM from the last day of the month to A5 for column A and
B5 for coulmn B(I have 2 column in which I type data) The code I have
here does the job, but only if you actually open the sheet at type
somthing that day. If I don't type in anything that day, then it does
not opdate and I would have skipped a month :(

any help appreciated

This is part of the code:


---------------------------------------------------------------------------Â*------------------------------------------



Jan:


With Target


If .Column < 1 Then GoTo Jan2
If DateNow < DateJan Then GoTo Feb


Application.EnableEvents = False
Range("I6").Value = Application.Sum(Range(Target, Range("A5")))



Application.EnableEvents = True


End With


Jan2:


With Target


If .Column < 2 Then GoTo InsertDate
If DateNow < DateJan Then GoTo Feb2


Application.EnableEvents = False
Range("J6").Value = Application.Sum(Range(Target, Range("B5")))

Application.EnableEvents = True


End With


---------------------------------------------------------------------------Â*------------------------------------------



- Column 1 and 2 are where I type in the amount of units I make.
- "I6" and "J6" are were they are stored.
- "InsertDate" is a third column were the date is.
- "DateJan" is the last date of the month where the data is sumed and
stored in the fields "I6" and "J6" and voila the graph apprears.
- "B5" is the first data field


But the big problem is that this only happens IF I type in anything
that particular date! If I don't make anything or it is a weekend then
im screwed! The graph just won't get updated...then the next month I
get the total number of units made for 2 month and not just 1 because
it didn't update the last month.


help thanks


San


Besvar »

how to auto update sum?
 

Well

what the code does is it sum the entire comlumn from the last entry to
the top. And if the last entry falls on say the 31-01-2006(last day of
month) the code register the entry and checks the date and since the
date is the is indeed the last day of the month the whole column is
sumed into cell [I6] for coulmn A and [J6] for column B.(I have 2
columns ith data)
Now I have the value for January stored in [I6] then Febuary the value
is stored in [I7] but this value is the value for this Feb + the value
from Jan, so I have to take [I7] -[I6] then I get the actual value for
Feb. The actual data is stored in column G5 for Jan nad G6 for Feb and
so on. This goes for the entire year.

This maybe a clumsy way of doing it, but its the only way I can make it
work.

The problem as you propably can see already is that if there is no
entry 31-01-2006 then then the value for jan [I6] will be blank. And
then in Feb the value will be the value Jan+Feb because [I6] is blank
then [I7]-[I6] = [I7].

The graph which gets the data from column G (which holds the actual
data for each month) and it will show Jan as blank and Feb as
dobble(showing both for Jan and Feb)

Hope somebody understands any of this.