Re: how to reduce execution time of my code?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



This might be quicker

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With wbSource.Worksheets("Flowdate")
For j = 24 To 36 Step 4
row_price = 2315

For i = 0 To 364
peak_price = .Cells(row_price, j).Value
If peak_price = "" Then
.Cells(row_price, j).Value = _
.Cells(row_price, j).Offset(0, 2).Value
End If

offpeak_price = .Cells(row_price, j + 1).Value
If offpeak_price = "" Then
.Cells(row_price, j + 1).Value = _
.Cells(row_price, j + 1).Offset(0, 2).Value
End If
row_price = row_price + 1
Next
MsgBox "Calculation done"
Next
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<catchchatur@xxxxxxxxx> wrote in message
news:1186666696.788754.284460@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Experts,

I am running my macro and its taking almost 5 minutes during execution
on my computer (2GB RAM). Is there any way by which I can reduce this
execution time? Here is the code...

With wbSource.Worksheets("Flowdate")
For j = 24 To 36 Step 4
row_price = 2315

For i = 0 To 364
peak_price = .Cells(row_price, j).Value
If peak_price = "" Then
Worksheets("Flowdate").Cells(row_price, j).Value =
Worksheets("Flowdate").Cells(row_price, j).Offset(0, 2).Value
End If

offpeak_price = .Cells(row_price, j + 1).Value
If offpeak_price = "" Then
Worksheets("Flowdate").Cells(row_price, j + 1).Value =
Worksheets("Flowdate").Cells(row_price, j + 1).Offset(0, 2).Value
End If
row_price = row_price + 1
Next
MsgBox "Calculation done"
Next
End With



.



Relevant Pages

  • Re: how to reduce execution time of my code?
    ... Your 4 lines reduced my code's execution time to 15 seconds... ... Both screenupdating and calculcation worked great for me. ... MsgBox "Calculation done" ...
    (microsoft.public.excel.programming)
  • RE: how to reduce execution time of my code?
    ... Perhaps turning off calculation if you have a lot ... lcalc = Application.Calculation ... MsgBox "Calculation done" ...
    (microsoft.public.excel.programming)
  • RE: Excel Calculation is faster when visible than when Visible=Fal
    ... Setting the calculation mode back to automatic is the CPU hog. ... Here I'm reusing the same excel ... Execution time when running with Excel instance visible ... I suggest we identify which part of code in ImportDataFromFile ...
    (microsoft.public.excel.programming)
  • Re: Speed up Excel execution
    ... where Excel builds a Dependency Tree to optimize the calculation sequence. ... Worksheets are scanned in the alphabetical order of their names, and inside a worksheet, cells are scanned from top left to bottom right. ... That type requires more memory and execution time than the others. ... a Lookup can be expressed in terms of an Index and a Match. ...
    (microsoft.public.excel.programming)
  • RE: Macro NOT completing
    ... I think it calculates before saving even if calculation is set to manual so ... MsgBox "after setting n1 value" ... MsgBox "the first line after ELSE (worksheet does not exist)" ... It all seems to hang up around the SaveAs macro, which is ran from near the ...
    (microsoft.public.excel.misc)