Re: Loop Macro taking too much time
I am already using Sumproduct function for it but it is a heavy file (12 MB) has multiple sheets and it keep calculating, therefore trying to reduce calculation time via macros.
Thanks again.
Re: Loop Macro taking too much time
I am already using Sumproduct function for it but it is a heavy file (12 MB) has multiple sheets and it keep calculating, therefore trying to reduce calculation time via macros.
Thanks again.
Re: Loop Macro taking too much time
Got it, last row for variable x was also set to column 1 it should be column 26. column 26 has values up to 397 rows and column 1 to 25 has data up to 4445 rows. After tweaking this process time is reduced to 41 seconds from 147 seconds.
Thanks you very much, your help is much appreciated.
Re: Loop Macro taking too much time
Thanks Pike for prompt response but it takes the same time, is it due to older version of excel ?
Re: Loop Macro taking too much time
Excel 2003
Re: Loop Macro taking too much time
Sample Sheet added
I am trying to get selected data from a macro but it's taking too much time, I am naive to excel macros don't know how to trouble shoot it.
Macro is as follows
Sub test()
Application.ScreenUpdating = False
Dim i As Integer, x As Integer
[AA3:AB400] = 0
For x = 3 To 397
For i = 2 To 4448
If Cells(i, 2) = Cells(x, 26).Value And Cells(i, 8) = "CO" _
Then Cells(x, 27).Value = Cells(x, 27).Value + Cells(i, 13).Value
If Cells(i, 2) = Cells(x, 26).Value And Cells(i, 8) = "DO" _
Then Cells(x, 28).Value = Cells(x, 28).Value + Cells(i, 13).Value
If Cells(i, 2) = Cells(x, 26).Value And Cells(i, 8) = "CS" _
Then Cells(x, 29).Value = Cells(x, 29).Value + Cells(i, 13).Value
If Cells(i, 2) = Cells(x, 26).Value And Cells(i, 8) = "DS" _
Then Cells(x, 30).Value = Cells(x, 30).Value + Cells(i, 13).Value
Next i
Next x
Application.ScreenUpdating = True
End Sub
Display More
Column reference are as follows:
2= Project code (All Number) in data
26= Output Project code for which data is required (Macro Output)
8 = Type of activity (CO, CS, DO, DS)
13= Number of hours
30=Hours (Macro Output)
Data sheet is as below
[ATTACH=CONFIG]68685[/ATTACH]