How to Speed up My Calculation

[Priyantha]

Dear All,

I have crated a salary arrears calculation sheet & used a mcro to calculate arrears. For this purpose i used, 3 sheets, first _ "Arrearsformat", Second _ "Outpt" & third _ "Deta". There are may employees details in "Deta" & arrears format in "Arrearsformat" sheet. Filtered data will be in "Outpt" sheet belongs to C4 _ Employee number in "Arrearsformat" sheet. I am susing fitered deta in "Outpt" sheet for more calculation in "Arrearsformat" sheet.

My mcro is working crrectly. But it get more time to compleet process. Even though i searched a lot about this, I edite many times my code but, i did not get a proprer answer. When i ran the macro Debug _ Step into mode, i noticed that there was some deley in the below part of code under 'Proceed"

"Evaluate("IFERROR(INDEX('" & ws2.Name & "'!G:G, MATCH(C4&""-""&A" & startrow + 2 & "-DAY(A" & startrow + 2 & ")+1, '" & ws2.Name & "'!C:C&""-""&'" & ws2.Name & "'!B:B, 0)),0)") ".

I used many times this part of code to get deta from "Outpt" sheet & for more calulations in the "Arrearsformat" sheet. I would like any advice or modifications from experts as this or other problems may exist.


Looking forward any help to solve this problem.

Thanks,

Priyantha

[Priyantha]

Does anyone help me? :

[HansV]

I'm sorry, but (1) the code is far too long and complicated to analyze, and (2) I have no idea how to speed it up.

[snb]

it's Data , not deta

Restrain reading/writing in a worksheet to once in VBA
Avoid the use of Ecelformulae in VBA, like all evaluate(' ... ")
You seem to have 2 workbooks in the workbook: 1 too many.
Avoid redundant Object Variables.

[ChrisGreaves]

My mcro is working crrectly. But it get more time to compleet process.

Priyantha let's first define your problem.
(1) How much time does it take your macro to complete the process (in hours, minutes, and seconds)
(2) How much do you want to speed up your calculation? That is, how long did you expect it to take? Or else, how long do you want it to take?
Thanks, Chris

[Priyantha]

My mcro is working crrectly. But it get more time to compleet process.

Priyantha let's first define your problem.
(1) How much time does it take your macro to complete the process (in hours, minutes, and seconds)
(2) How much do you want to speed up your calculation? That is, how long did you expect it to take? Or else, how long do you want it to take?
Thanks, Chris

Chris,

It takes the following times for one peocess.

Time is :24.66796875 _ For Six Month Period Complet Process (Start Date 2023/11/01 End Date 2024/04/29)
Time is :77.640625 _ For One Year Period Complet Process (Start Date 2023/05/01 End Date 2024/04/29)
Time is :168.277343 _ For Two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29)

I hope to do two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29) about 8/10 seconds.

Thanks,
Priyantha.

[Priyantha]

it's Data , not deta

Restrain reading/writing in a worksheet to once in VBA
Avoid the use of Ecelformulae in VBA, like all evaluate(' ... ")
You seem to have 2 workbooks in the workbook: 1 too many.
Avoid redundant Object Variables.

SNB,

There many detas in my "Deta" Sheet (about 50,000 rows) but cant upload sample sheet with all deta.
What can be done to get deta for "Evaluate function". I accepted any modification.

Thanks,
Priyantha.

[ChrisGreaves]

It takes the following times for one peocess.
Time is :24.66796875 _ For Six Month Period Complet Process (Start Date 2023/11/01 End Date 2024/04/29)
Time is :77.640625 _ For One Year Period Complet Process (Start Date 2023/05/01 End Date 2024/04/29)
Time is :168.277343 _ For Two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29)
I hope to do two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29) about 8/10 seconds.

Priyantha, thank you for this data. If I understand it then you are looking for a speed up from 24 or 168 seconds to 8 or ten seconds.
This translates to a speed increase anywhere from 2.4 times to 20 times as fast, depending on the data?
That would be an acceptable measure of any solution?
Cheers, Chris

[Priyantha]

It takes the following times for one peocess.
Time is :24.66796875 _ For Six Month Period Complet Process (Start Date 2023/11/01 End Date 2024/04/29)
Time is :77.640625 _ For One Year Period Complet Process (Start Date 2023/05/01 End Date 2024/04/29)
Time is :168.277343 _ For Two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29)
I hope to do two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29) about 8/10 seconds.

Priyantha, thank you for this data. If I understand it then you are looking for a speed up from 24 or 168 seconds to 8 or ten seconds.
This translates to a speed increase anywhere from 2.4 times to 20 times as fast, depending on the data?
That would be an acceptable measure of any solution?
Cheers, Chris

I will accept anything that takes less time than it currently takes. It is better if the formulas related to the calculations can be included in an excel sheet

Thanks
Priyantha

[Priyantha]

It takes the following times for one peocess.
Time is :24.66796875 _ For Six Month Period Complet Process (Start Date 2023/11/01 End Date 2024/04/29)
Time is :77.640625 _ For One Year Period Complet Process (Start Date 2023/05/01 End Date 2024/04/29)
Time is :168.277343 _ For Two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29)
I hope to do two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29) about 8/10 seconds.

Priyantha, thank you for this data. If I understand it then you are looking for a speed up from 24 or 168 seconds to 8 or ten seconds.
This translates to a speed increase anywhere from 2.4 times to 20 times as fast, depending on the data?
That would be an acceptable measure of any solution?
Cheers, Chris

Dear ChrisGreaves,

I did iit

[SpeakEasy]

You mean you've managed to speed things up?