How to improve this macro to run faster

Hi jerry,
I want to see data and formulas in your file. Can you attach it?

Hi Huuthang_hd,

I have tried to attach my file but failed to do so, do you have any email? so that I cand send the file to you.

Thanks.

Hi all,

I'm sorry for that as I did not read the regulation thoroughly for cross posted my question. I just send my question to the website as above only. No more than that.

Anyway, the problem is unsolved yet.

Sixthsence,
I tried to follow your instruction to attach my file but still failed to upload. there is a notice that "upload of file is failed".

But do hope somebody can help me on this macro problem.

Dear FDibbins?Sixthsense,

My file is more than 3mb size. However, I will try to attach the lite version with the formula. The difference is the one that I tried to attached before come with daily time series from 2000 to 2049. The one that I attach now is come with monthly time series. The vba module is same.

If anyone can help me on this to make my macro faster, I really appreciate it. I intent to use it in my sheet that come with daily time series. I know there is a lot of software such as Hec-ressim that do a work for me, but I'm more comfortable working using spreadsheet.

jerry

Sixthsense,

I got this error while running your code as above.

By the way, thanks for the suggestion. Will look into it also.

The values above are number. But the values are refer to the tab before it.

I'm still try to run it but always got no responding notice.

I will try to work it out at this moment. I will update you if I'm able to run it with your code.

I'd imagine it 'stops responding' because it is running but not providing you with notifications. It will run faster, and complete eventually.

I hope that would be the case. But it showing at the above of my spreadsheet that it is not responding. That make me think that it is not running. The screen in white blur when iI move the cursor. Anyway, thanks for the reply.

Thanks very much abousetta. I will try your code later as at the moment I'm attending 3 days conference at my place. I will update you with the outcome by running your suggested code in my excel sheet.

Hope it will work this time.

abousetta,

I tried to run your code and found this error.
Run-time error '6':
Overflow


Line 15 is highlighted with yellow colour.

Sub Button1_Click()
Dim x&, y&, A&, B&, C&, D&, vE7Value&, vK13Value&, vK14Value&
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
x = Cells(18, 2).Value + 20
vE7Value = Cells(7, 5).Value
vK13Value = Cells(13, 11).Value
vK14Value = Cells(14, 11).Value
For y = 21 To x
A = 0 ' First run plant discharge starts at 0.
B = Cells(y, 6).Value
C = Cells(y, 21).Value
D = 0.98 * vK13Value
Do While B >= vE7Value ' Current reservoir level above MOL
A = A + 5 ' Increment @ 5.
If C >= D Then Exit Do ' almost there.
Loop
Do While B >= vE7Value
A = A + 0.1
If C >= vK13Value Then Exit Do ' Rated power is achieved.
Loop
If C < vK14Value Then Cells(y, 8).Value = 0 Else Cells(y, 8).Value = A
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub