reduce code execution time

  • Re: reduce code execution time

    I don't understand what you are saying but just rename the "test".

    I don't recommend to use "Count" as a procedure name, coz it is a vb reserved word.

    I'm going off line now, so tomorrow.

  • 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".

    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)


    Special note:
    I have changed the line

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


    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?/

  • Re: reduce code execution time

    jindon this is awesome. you just saved me a ton of time! :)
    ty sir..

    one additional query can the first macro "update" be run in an array only the deletion part

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!