Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
Hello All,
The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
Will this function ever be called from a worksheet formula?
If yes, then this won't work. If no, then maybe just looping through the cells that contain numbers would be quicker (skipping empty cells, text cells): Dim myNumConst As Range Dim myNumFormulas As Range Dim myNumCells As Range Set myNumCells = Nothing Set myNumConst = Nothing Set myNumFormulas = Nothing On Error Resume Next Set myNumConst _ = SearchRange.Cells.SpecialCells(xlCellTypeConstants , xlNumbers) Set myNumFormulas _ = SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myNumConst Is Nothing Then Set myNumCells = myNumFormulas Else If myNumFormulas Is Nothing Then Set myNumCells = myNumConst Else Set myNumCells = Union(myNumConst, myNumFormulas) End If End If If myNumCells Is Nothing Then 'return nothing Else 'do the work against the smaller range For Each rCell In myNumCells.Cells '... next rCell End If ========== But if that whole search range is numbers, then this won't help. Sisilla wrote: Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
Hello Don,
Thank you for your efforts to help me. I am afraid that I don't understand how CountIf will save me iterations within the For loop. I can count the number of cells in the range that are greater than CompareValue, but won't I still have to compare each cell in the range, or am i missing something? I appreciate any further help. Thanks, Sisilla Don Guillett wrote: why not just use COUNTIF? -- Don Guillett SalesAid Software "Sisilla" wrote in message ps.com... Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
After reading Don's reply, you could use his check to know when to quit looking:
Option Explicit Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range Dim HowMany As Long Dim myCount As Long Dim myNumConst As Range Dim myNumFormulas As Range Dim myNumCells As Range Dim TotalRng As Range Dim rCell As Range HowMany = Application.CountIf(SearchRange, "=" & CompareValue) If HowMany = 0 Then 'return nothing and get out Set AtLeastCells = Nothing Exit Function End If Set myNumConst = Nothing Set myNumFormulas = Nothing On Error Resume Next Set myNumConst _ = SearchRange.Cells.SpecialCells(xlCellTypeConstants , xlNumbers) Set myNumFormulas _ = SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myNumConst Is Nothing Then Set myNumCells = myNumFormulas Else If myNumFormulas Is Nothing Then Set myNumCells = myNumConst Else Set myNumCells = Union(myNumConst, myNumFormulas) End If End If If myNumCells Is Nothing Then 'shouldn't get here, since we know there's at least one match Else myCount = 0 Set TotalRng = Nothing For Each rCell In myNumCells.Cells If rCell.Value = CompareValue Then myCount = myCount + 1 If TotalRng Is Nothing Then Set TotalRng = rCell Else Set TotalRng = Union(rCell, TotalRng) End If If myCount = HowMany Then 'done looking Exit For End If End If Next rCell End If Set AtLeastCells = TotalRng End Function (Untested, but it did compile) Dave Peterson wrote: Will this function ever be called from a worksheet formula? If yes, then this won't work. If no, then maybe just looping through the cells that contain numbers would be quicker (skipping empty cells, text cells): Dim myNumConst As Range Dim myNumFormulas As Range Dim myNumCells As Range Set myNumCells = Nothing Set myNumConst = Nothing Set myNumFormulas = Nothing On Error Resume Next Set myNumConst _ = SearchRange.Cells.SpecialCells(xlCellTypeConstants , xlNumbers) Set myNumFormulas _ = SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myNumConst Is Nothing Then Set myNumCells = myNumFormulas Else If myNumFormulas Is Nothing Then Set myNumCells = myNumConst Else Set myNumCells = Union(myNumConst, myNumFormulas) End If End If If myNumCells Is Nothing Then 'return nothing Else 'do the work against the smaller range For Each rCell In myNumCells.Cells '... next rCell End If ========== But if that whole search range is numbers, then this won't help. Sisilla wrote: Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
Hello Dave,
Your suggestions are very helpful. I won't be using the function as a worksheet formula. The SeachRange will always be in the number format, but there are many instances when I expect many empty cells within the range. I will try your suggestion of skipping empty cells. Thanks very much. I appreciate it. Sisilla Dave Peterson wrote: Will this function ever be called from a worksheet formula? If yes, then this won't work. If no, then maybe just looping through the cells that contain numbers would be quicker (skipping empty cells, text cells): Dim myNumConst As Range Dim myNumFormulas As Range Dim myNumCells As Range Set myNumCells = Nothing Set myNumConst = Nothing Set myNumFormulas = Nothing On Error Resume Next Set myNumConst _ = SearchRange.Cells.SpecialCells(xlCellTypeConstants , xlNumbers) Set myNumFormulas _ = SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myNumConst Is Nothing Then Set myNumCells = myNumFormulas Else If myNumFormulas Is Nothing Then Set myNumCells = myNumConst Else Set myNumCells = Union(myNumConst, myNumFormulas) End If End If If myNumCells Is Nothing Then 'return nothing Else 'do the work against the smaller range For Each rCell In myNumCells.Cells '... next rCell End If ========== But if that whole search range is numbers, then this won't help. Sisilla wrote: Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
Dave,
I cannot express how thankful I am to you. I greatly appreciate your time and effort. Now that you've explained it, I see how helpful CountIf can be in saving iterations, especially if there is no instance in SearchRange greater than CompareValue and also if the instances are closer to the top of the range. I will definitely try out your new suggestions. Again Thanks, Sisilla Dave Peterson wrote: After reading Don's reply, you could use his check to know when to quit looking: Option Explicit Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range Dim HowMany As Long Dim myCount As Long Dim myNumConst As Range Dim myNumFormulas As Range Dim myNumCells As Range Dim TotalRng As Range Dim rCell As Range HowMany = Application.CountIf(SearchRange, "=" & CompareValue) If HowMany = 0 Then 'return nothing and get out Set AtLeastCells = Nothing Exit Function End If Set myNumConst = Nothing Set myNumFormulas = Nothing On Error Resume Next Set myNumConst _ = SearchRange.Cells.SpecialCells(xlCellTypeConstants , xlNumbers) Set myNumFormulas _ = SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myNumConst Is Nothing Then Set myNumCells = myNumFormulas Else If myNumFormulas Is Nothing Then Set myNumCells = myNumConst Else Set myNumCells = Union(myNumConst, myNumFormulas) End If End If If myNumCells Is Nothing Then 'shouldn't get here, since we know there's at least one match Else myCount = 0 Set TotalRng = Nothing For Each rCell In myNumCells.Cells If rCell.Value = CompareValue Then myCount = myCount + 1 If TotalRng Is Nothing Then Set TotalRng = rCell Else Set TotalRng = Union(rCell, TotalRng) End If If myCount = HowMany Then 'done looking Exit For End If End If Next rCell End If Set AtLeastCells = TotalRng End Function (Untested, but it did compile) Dave Peterson wrote: Will this function ever be called from a worksheet formula? If yes, then this won't work. If no, then maybe just looping through the cells that contain numbers would be quicker (skipping empty cells, text cells): Dim myNumConst As Range Dim myNumFormulas As Range Dim myNumCells As Range Set myNumCells = Nothing Set myNumConst = Nothing Set myNumFormulas = Nothing On Error Resume Next Set myNumConst _ = SearchRange.Cells.SpecialCells(xlCellTypeConstants , xlNumbers) Set myNumFormulas _ = SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myNumConst Is Nothing Then Set myNumCells = myNumFormulas Else If myNumFormulas Is Nothing Then Set myNumCells = myNumConst Else Set myNumCells = Union(myNumConst, myNumFormulas) End If End If If myNumCells Is Nothing Then 'return nothing Else 'do the work against the smaller range For Each rCell In myNumCells.Cells '... next rCell End If ========== But if that whole search range is numbers, then this won't help. Sisilla wrote: Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
You have answers to your main question but just to add, if your
'AtLeastCells' cells will exist in many non-contiguous areas your union loop will become exponentially slower, eventually to a crawl. If that's a possibility consider not making a single large multi area range object and processing in a different way. Eg make an array of string addresses for later use, or process intermediate range objects that exceed say 100 areas. Regards, Peter T "Sisilla" wrote in message ps.com... Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
Hello Peter,
Thanks for your reply. Are you saying that it would be faster to concatenate strings with every iteration of the loop than to union non-contiguous ranges? If this is indeed what you are saying, then I will have to find some way to convert the returned string back to a range once it is needed (in the Copy method). Thank you for your advice. Sisilla Peter T wrote: You have answers to your main question but just to add, if your 'AtLeastCells' cells will exist in many non-contiguous areas your union loop will become exponentially slower, eventually to a crawl. If that's a possibility consider not making a single large multi area range object and processing in a different way. Eg make an array of string addresses for later use, or process intermediate range objects that exceed say 100 areas. Regards, Peter T "Sisilla" wrote in message ps.com... Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
If you are going to concatenate strings, with intermediates separated with
commas, don't do more than say 12 at a time before converting to a range or you might exceed the 255 address limit. As I mentioned union is only slow if you are likely to end up will large multiarea ranges (note I don't mean multicell). Which way to cater really would depend on your ultimate objective with the range object(s). You say in the Copy method' but you can't use the copy method with non-contiguous areas anyway, so I'm not quite sure what you are doing. Regards, Peter T "Sisilla" wrote in message ups.com... Hello Peter, Thanks for your reply. Are you saying that it would be faster to concatenate strings with every iteration of the loop than to union non-contiguous ranges? If this is indeed what you are saying, then I will have to find some way to convert the returned string back to a range once it is needed (in the Copy method). Thank you for your advice. Sisilla Peter T wrote: You have answers to your main question but just to add, if your 'AtLeastCells' cells will exist in many non-contiguous areas your union loop will become exponentially slower, eventually to a crawl. If that's a possibility consider not making a single large multi area range object and processing in a different way. Eg make an array of string addresses for later use, or process intermediate range objects that exceed say 100 areas. Regards, Peter T "Sisilla" wrote in message ps.com... Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
I use the Copy() method on non-contigous cells and ranges all the time.
I guess I don't know what you mean by "multiarea" and "non-contiguous area." I have never heard these terms in an Excel context. However, you raise a good point with the address limit. In any case, Dave's suggestions have considerably speeded up my run times, but thank you for your input. Peter T wrote: If you are going to concatenate strings, with intermediates separated with commas, don't do more than say 12 at a time before converting to a range or you might exceed the 255 address limit. As I mentioned union is only slow if you are likely to end up will large multiarea ranges (note I don't mean multicell). Which way to cater really would depend on your ultimate objective with the range object(s). You say in the Copy method' but you can't use the copy method with non-contiguous areas anyway, so I'm not quite sure what you are doing. Regards, Peter T "Sisilla" wrote in message ups.com... Hello Peter, Thanks for your reply. Are you saying that it would be faster to concatenate strings with every iteration of the loop than to union non-contiguous ranges? If this is indeed what you are saying, then I will have to find some way to convert the returned string back to a range once it is needed (in the Copy method). Thank you for your advice. Sisilla Peter T wrote: You have answers to your main question but just to add, if your 'AtLeastCells' cells will exist in many non-contiguous areas your union loop will become exponentially slower, eventually to a crawl. If that's a possibility consider not making a single large multi area range object and processing in a different way. Eg make an array of string addresses for later use, or process intermediate range objects that exceed say 100 areas. Regards, Peter T "Sisilla" wrote in message ps.com... Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf Greater Than/Find Greater Than
I use the Copy() method on non-contigous cells and ranges all the time.
Do both these work for you - Range("A1:A2,C1:C2").Copy Range("A1:A2,B3:B4").Copy or only the first? If your union only builds a few hundred areas ignore what I mentioned about this aspect. Regards, Peter T "Sisilla" wrote in message ps.com... I use the Copy() method on non-contigous cells and ranges all the time. I guess I don't know what you mean by "multiarea" and "non-contiguous area." I have never heard these terms in an Excel context. However, you raise a good point with the address limit. In any case, Dave's suggestions have considerably speeded up my run times, but thank you for your input. Peter T wrote: If you are going to concatenate strings, with intermediates separated with commas, don't do more than say 12 at a time before converting to a range or you might exceed the 255 address limit. As I mentioned union is only slow if you are likely to end up will large multiarea ranges (note I don't mean multicell). Which way to cater really would depend on your ultimate objective with the range object(s). You say in the Copy method' but you can't use the copy method with non-contiguous areas anyway, so I'm not quite sure what you are doing. Regards, Peter T "Sisilla" wrote in message ups.com... Hello Peter, Thanks for your reply. Are you saying that it would be faster to concatenate strings with every iteration of the loop than to union non-contiguous ranges? If this is indeed what you are saying, then I will have to find some way to convert the returned string back to a range once it is needed (in the Copy method). Thank you for your advice. Sisilla Peter T wrote: You have answers to your main question but just to add, if your 'AtLeastCells' cells will exist in many non-contiguous areas your union loop will become exponentially slower, eventually to a crawl. If that's a possibility consider not making a single large multi area range object and processing in a different way. Eg make an array of string addresses for later use, or process intermediate range objects that exceed say 100 areas. Regards, Peter T "Sisilla" wrote in message ps.com... Hello All, The following code runs slowly. Is there a better way to do this, perhaps with CountIf and Find? If there is even the smallest improvement from comparing every cell in the SearchRange with CompareValue, I'd love to hear the solution! Function AtLeastCells(CompareValue As Integer, SearchRange As Range) As Range 'Searches SearchRange for values that are greater than or equal to CompareValue 'of Integer Data Type. 'If values are found, all matching cells are returned. 'If no value is found, an empty range is returned. Dim rCell As Range For Each rCell In SearchRange.Cells If rCell.Value = CompareValue Then Set AtLeastCells = UnionWithNothing(rCell, AtLeastCells) End If Next rCell End Function I greatly appreciate any help. Thanks! Sisilla |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF greater than one column and less than another | Excel Worksheet Functions | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
countif greater/less than argument | Excel Worksheet Functions | |||
Using COUNTIF to find numbers within a range greater than the mean | Excel Worksheet Functions | |||
countif a date is greater than today | Excel Worksheet Functions |