reduce code execution time

  • Re: reduce code execution time


    1) My understanding:[INDENT]- "Dashboard" has "Emp Id" with "Startdt" and "Finishdt" for each assignment.[/INDENT]
    [INDENT]- "Temp Calc" has selected "Employee" and "Start Date" and "End Date" that indicates the dates of list.
    - Add 1 to the corresponding cell when any date of "Temp Calc" matches any date falls in between "Startdt" and "Finishdt".


    [/INDENT]
    If you write a code to do above using only Loop or Find method, it costs too much because you have so many rows as you have mentioned somewhere in the beginning of this thread.


    2 points to speed up the process in this case is--


    [INDENT]- Speed up the Loop. (use of array variable to store data)
    - Minimize the number of Loop. (use of Dictionary object to store needed info)
    - Avoid too may access to Cells/Range object to Read/write the values. (most important factor)


    [/INDENT]


    Special note:
    I have changed the line

    Code
    1. If (e <= StartDate) * (s >= EndDate) Then


    to

    Code
    1. If (e <= EndDate) * (s >= StartDate) Then


    to check if the date(s) between "Startdt" and "Finishdt" falls in the dates between "Start Date" and "End Date".

  • Re: reduce code execution time


    Good morning Jindon,


    one small misunderstanding the dashboard contains all the emp id and the user compare date range.
    temp calc has the employee's assignment data so what i wish to do is...
    using the emp id in dashboard as a reference i must traverse temp calc sheet and find out the number of times the emp id is present and return a count against the employee in the dashboard sheet under the days you can see for the number of times it falls in between the compare date only/......


    is this what you are saying?/