Delay in calculation and value assignment to cells

[MI man]

I have an event in VBA:

CalcVal_Click()
dim startdate as date
dim i as integer
startdate= date(month) & "/" & 19 & "/" & date(year)

For i = 2 to 20000
If sheets("Data").cells(i,4).value="" then
Sheets("Measure").cells(i,2).value=Empty
Else
Sheets("Measure").cells(i,2).value = Sheets("Data").cells(i,4).value - startdate
End if
Next
End Sub

But, this For loop calculations are taking a considerable amount of time...say 2 to 5 seconds to calculate for each 'i' and assigning the value to the target cells, ultimately consuming a huge time for all the 20000 cells...(sometimes, the application gets hung up and one has to force close it through Task Manager in which the status is shown as "Not Responding".)

Any way to optimise this...???

PS: The total Excel application including the data and some other built-in functions and array formulae and VBA takes the size of 9.5 Mb

 

[chergh]

Try turning screenupdating off and setting the calculation mode to manual then turn the screenupdating back on at the end of your procedure and set the calculation mode back to automatic at the end, also probably worth putting in an application.calculate after you change the calculation mode back to automatic.

 

[MI man]

Thanks for the response chergh.

Would try..!!

 

[MI man]

Thanks for the response chergh.

Would try..!!

And Kudos to you....This works very well...

Thanks a lot...!!!