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