Faster way to loop

[BRB1983]

I would like to know if there is a faster way for a loop to run. Here is what i have now and it is slow.

  Dim rng As Range    Dim lColorCounter As Long
    Dim rngCell As Range
    Set rng = Sheets("MASTER LINE LIST").Range("C2:Z2000")
    lColorCounter = 0
        For Each rngCell In rng
        'Checking BLUE color
            If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(0, 176, 240) Then
                lColorCounter = lColorCounter + 1
            End If
        Next
    Sheets("Status").Range("C5") = lColorCounter
    lColorCounter = 0
        For Each rngCell In rng
        'Checking Yellor color
            If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(255, 255, 0) Then
                lColorCounter = lColorCounter + 1
            End If
        Next
    Sheets("Status").Range("B5") = lColorCounter

 

[Peter_SSs]

I do still think that there is something amiss with the machine the OP is using.

Could be but it could be that it is just older/less powerful and/or other stuff, perhaps even unrelated to Excel is happening. For example, I have just run the tests again on the identical data and received results closer to what you reported which is quite a variance from my earlier posted results.

3.070
1.563
0.063


Never-the-less I would contend that a significant & noticeable speed increase (of the order of 20x) has been achieved by finding the detail of the OP's workbook and a change of approach.

 

[igold]

Never-the-less I would contend that a significant & noticeable speed increase (of the order of 20x) has been achieved by finding the detail of the OP's workbook and a change of approach.

Yes, clearly your approach was much quicker than mine, which clearly shows the difference between my immature coding skills compared to your more mature skills.

Thanks again for taking the time to show me.

 

[BRB1983]

Hi guys, sorry i'm responding late. I will give the provided info a try and let you know how they work. i am currently tied up with Clients and Budget issues. I will get back with you all as soon as possible. Thanks.

 

[BRB1983]

Peter, The last code you ran is super fast,but the count is somehow off. The results from the code are as followed: Blue = 1285 Yellow = 67
those two counts together is 1352. which is exactly how many that are colored. i manually Counted each color and count should be as followed: Blue = 101 Yellow = 1251

 

[Peter_SSs]

Y.. which clearly shows the difference between my immature coding skills compared to your more mature skills.

There was nothing at all wrong with your coding.
When you (& John) wrote your code, we didn't know details of how the colouring of the cells came about so the only way to determine the colour was to individually inspect every cell - as you both did.

The OP's use of 'DisplayFormat' in the original code is what triggered my thinking about CF and hence my questions about that. Once we had more information, alternatives became available.

.. the count is somehow off. The results from the code are as followed: Blue = 1285 Yellow = 67
those two counts together is 1352. which is exactly how many that are colored. i manually Counted each color and count should be as followed: Blue = 101 Yellow = 1251

I don't have your file to test but that result would lead me to think that you have some flange numbers in both 'Circulation' and 'Final'.

1. Is that the case?
2. If so, should that happen?
3. What does your CF do with that circumstance? Does 'Final' over-ride 'Circulation' for example? That is, if a flange number appears in both lists, does the cell get coloured yellow or blue?

Actually, no need to answer the above questions if changing this section of the code does the trick. I have just swapped the order of checking the blue/yellow so that the 'Final' (yellow) list over-rides the 'Circulation' (blue) list.

If Not IsEmpty(a(i, j)) Then
  If dy.exists(a(i, j)) Then
    lyellowcounter = lyellowcounter + 1
  ElseIf db.exists(a(i, j)) Then
    lbluecounter = lbluecounter + 1
  End If
End If

 

[igold]

There was nothing at all wrong with your coding.
When you (& John) wrote your code, we didn't know details of how the colouring of the cells came about so the only way to determine the colour was to individually inspect every cell - as you both did.

The OP's use of 'DisplayFormat' in the original code is what triggered my thinking about CF and hence my questions about that. Once we had more information, alternatives became available.

[/code]

Just the fact that you thought of interrogating each cell is what I was trying to convey. It never would have occurred to me that the manner which the cells colored would lead to a faster way to get a count. Well done.

 

[Peter_SSs]

The speed difference basically comes from the change in number of interactions between the code and worksheets. Such interactions are relatively slow and checking the DisplayFormat requires (nearly) 48,000 interactions. My code has 3 interactions only (grab the values from the 3 relevant sheets) to get the data to make the counts. Then all the work is done in memory before finally writing the results back to a worksheet. My code also checks in case there are less than 2000 rows or less than 24 columns actually used, perhaps saving checking a lot of empty cells at the bottom or right.

 

[igold]

The colour in the OP's cells are there by Conditional Formatting, not direct application. Without using DisplayFormat your code would not pick up the colour applied by the CF.

Hi Peter, may I ask you one more question...

Does the above mean that a given cell could have two different simultaneous colors that could be used to address that cell.

 

[BRB1983]

yes flange number will end up on circulation then in final. final overrules circulation, and the code in post 35, works perfectly.

 

[Peter_SSs]

yes flange number will end up on circulation then in final. final overrules circulation, and the code in post 35, works perfectly.

Cheers. Thanks for the confirmation.

Does the above mean that a given cell could have two different simultaneous colors that could be used to address that cell.

No, a cell can only have one colour, but the cell could meet 2 or more of the CF rules. If the cell meets 2 or more CF conditions, the format of the last one that is true will be applied unless an earlier condition has 'Stop if True' set.