Macro to automate simple routine

[Sinner]

Hi,

I have this sheet with almost all formulas of sumproduct. Calculations
based on start/end dates.
It takes quite some time calculating all the formulas.
Do expand the columns to see the sumproduct formulas.

Here I would like to have some macro to run after I mention dates and
calculate all the results.

Sample file is at http://www.savefile.com/files/1621169

Thx.

 

[Don Guillett]

without looking at your file just use, where b1 has a desired date
=sumproduct((a2:a22>=b1 etc

 

[Don Guillett]

I went back and looked at your file. I misunderstood the question. However,
it seems that your formulasations could be modied since you only want the
totals
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM=$F$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM=$g$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM=$hF$5),(AmtList))
to this. Note that you MUST spell it out and can NOT use the reference as
above. Note the { }
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM={"cash","cheque","credit
card"}),(AmtList))

 

[Sinner]

I went back and looked at your file. I misunderstood the question. However,
it seems that your formulasations could be modied since you only want the
totals
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM=$F$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM=$g$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM=$hF$5),(AmtList))
to this. Note that you MUST spell it out and can NOT use the reference as
above. Note the {    }
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM={"cash","cheque","credit
card"}),(AmtList))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- Show quoted text -

Hi Mike,


Thanks for reading the post.
Yes data is in another sheet.
I have named ranges such as ActList for activity list, datelist for
date column, CEList for coll/enter status differentiation, PM for mode
of payment & Amtlist for amount list.
In my sumproduct formula I'm using all these ranges since they change
frequently.

The Picture you noted was a button. By default I have set sheet
calculation to manual.
After I have input data in the data sheet, I put dates & click this
button to calculate. Since formulas are bulky, it takes sometime to
calculate & leaving it to automatic tends to slow up the process. Once
I'm done with data input, then I move on to calculate & finally save.

All I wanted was to have a macro code to get me same figures from the
data table, knowing the formulas that I'm currently using & same name
ranges.

Thx.

--------------------------------
Hieee Don Guillett,

Good to hear from u : )
Thnx for the additional point regarding referencing.

It's a breakup sheet which is pretty simple I guess.
Any alternate approach to get same format breakup results is
welcome : )


Takecare

 

[Don Guillett]

try this idea

Application.enableevents=false
calculate
Application.EnableEvents=True
rest of code

It's too bad that you didn't bother to see the value of my suggestion to cut
down your calculation time.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I went back and looked at your file. I misunderstood the question.
However,
it seems that your formulasations could be modied since you only want the
totals
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM=$F$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM=$g$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM=$hF$5),(AmtList))
to this. Note that you MUST spell it out and can NOT use the reference as
above. Note the { }
=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList­=Coll),--(PM={"cash","cheque","credit
card"}),(AmtList))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message







- Show quoted text -

Hi Mike,


Thanks for reading the post.
Yes data is in another sheet.
I have named ranges such as ActList for activity list, datelist for
date column, CEList for coll/enter status differentiation, PM for mode
of payment & Amtlist for amount list.
In my sumproduct formula I'm using all these ranges since they change
frequently.

The Picture you noted was a button. By default I have set sheet
calculation to manual.
After I have input data in the data sheet, I put dates & click this
button to calculate. Since formulas are bulky, it takes sometime to
calculate & leaving it to automatic tends to slow up the process. Once
I'm done with data input, then I move on to calculate & finally save.

All I wanted was to have a macro code to get me same figures from the
data table, knowing the formulas that I'm currently using & same name
ranges.

Thx.

--------------------------------
Hieee Don Guillett,

Good to hear from u : )
Thnx for the additional point regarding referencing.

It's a breakup sheet which is pretty simple I guess.
Any alternate approach to get same format breakup results is
welcome : )


Takecare

 

[Sinner]

try this idea

Application.enableevents=false
  calculate
Application.EnableEvents=True
rest of code

It's too bad that you didn't bother to see the value of my suggestion to cut
down your calculation time.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

On Jun 21, 8:16 pm, "Don Guillett" <[email protected]> wrote:

Don I tried that : ) I placed all three modes together as you said.
={"cash","cheque","credit card"}

but for no cheque & credit card, it is giving a #value error.
so by placing only ={"cash"}, it is working.
The other two may or may not be there but still I would like em to be
calculated. There could also be debit card, pay order & demand
draft : )

How about a data validation, if i select from a cell the mode that I
want or tick all to select the kind of information the grid should
show.
How abt I select the modes : ) wicked ehh

Thx.

 

[Don Guillett]

Let me see YOUR entire formula.
I can't understand why you could get that result from the formula if entered
properly unless a matter of leading/trailing spaces. Send the other file if
you like so I can test. Or, you may try trimming your source as shown below

=SUMPRODUCT(--(ActList=$E6),--(DateList>=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(trim(PM)={"cash","cheque","credit
card"}),(AmtList))

Your other idea will not work in this context because, as I said, it must be
spelled out in the formula. Of course, a macro could create the formula.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

try this idea

Application.enableevents=false
calculate
Application.EnableEvents=True
rest of code

It's too bad that you didn't bother to see the value of my suggestion to
cut
down your calculation time.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

On Jun 21, 8:16 pm, "Don Guillett" <[email protected]> wrote:

Don I tried that : ) I placed all three modes together as you said.
={"cash","cheque","credit card"}

but for no cheque & credit card, it is giving a #value error.
so by placing only ={"cash"}, it is working.
The other two may or may not be there but still I would like em to be
calculated. There could also be debit card, pay order & demand
draft : )

How about a data validation, if i select from a cell the mode that I
want or tick all to select the kind of information the grid should
show.
How abt I select the modes : ) wicked ehh

Thx.