simplify & faster process in marco

**kweep** · 04-06-2015, 12:27 PM

Hello,
just encounter a problem which is the main problem of using macro for a complex situation..is that too many condition and calculation takes too much time..which should be a faq for macro in fact?
so here's the condition...
i have a list of pricing data e.g. below

A B C D E F G H
cus# | cus name | SKU # | SKU name | available from | available to | price cate | amount |
1 abc ltd 001 001A 1/1/15 28/02/15 cate A 10

distributed from column A to H
and im writing a form to call price summary for a specific sku for all price cate in each date in the date range required

here is the template for the summary shows how it should looks like..

Please Login or Register  to view this content.

and heres the data & condition to call

Please Login or Register  to view this content.

above just a demo of how it should call from the source (mysheet) to the objective (newsheet) by meeting condition in AND condition
1. objective date => mysheet from
2. objective date <= mysheet to
3. objective cus = mysheet cus
4. objective sku = mysheet sku
5. objective price cate = specific price cate

the result should look like this..
date | cus # | cus name | sku # | sku name | cate A | cate B | ...... | cate Z |
1/1/15 1 abc ltd 001 001A 10 10
2/1/15 1 abc ltd 001 001A 10 10
3/1/15 1 abc ltd 001 001A 10 10
.
.
.
28/2/15 1 abc ltd 001 001A 10 20

there are around 20 price cate and predictable 3000 lines in raw data
i've just written code for 2 price cate and find that the process time will be extremely slow while doing for 20 price cate with 7xx lines in raw only..
i could hardly imagine how many time it will take when reaching 3000 lines..
i know i didnt do it smartly but is there any way to improve the performance?

many thanks for your patient & reading