Can someone help me to condense this VBA code - it takes a long time to compute

[shayej]

I am trying to carry out a sensitivity analysis, result are presented in a table like format below:

SAtable.png


Thank you for your help.

Please Login or Register  to view this content.

the code for CopyUntilZero is below:

Please Login or Register  to view this content.

[JohnTopley]

Please see yellow banner on how to attach a sample workbook together with an explanation of what you are doing rather than us having to decipher the code.

[shayej]

Sample worksheet ED.xlsm

Hi I have now attached a simple file.

basically I begin with inserting a value in F3 and E26 in Rev + Cost worksheet.


cashflow sheet calculates cashflow over time.

and when I go to Sensitivity analysis sheet and insert a step size, it changes F3 and E26 values and calculates RV value when cashflow reaches 0 using CopyUntilZero.

i hope this explains the steps properly.

[Artik]

The first thing I see that slows down the execution of the code is using the clipboard to copy data, for example, instead of

Please Login or Register  to view this content.

you can write

Please Login or Register  to view this content.

and instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

You can also abandon Select, which also significantly slows down the operation.
Below is the code after improvement. I do not exclude that it can be further optimized, but this is a job not for me.

Please Login or Register  to view this content.

Artik

[shayej]

Thank you so much, it did make the whole process much faster.

[shayej]

it used to take 1 min to finish the analysis now it takes just under 8 seconds