Object variable or With block variable not set

[brulet]

Any idea why the error "Object variable or With block variable not set"? Works fine with a test routine, but not in a sub
IE

<code>
Sub testsmallest()
msgbox smallest(0.001, Worksheets("Site1.Frequency.1").Range("C1:C61")).Cells.Offset(0, -1)
End Sub
</code>

but not...

<code>
Function findNearest(referenceFlow As Double, findInMonth As Worksheet) As Variant
Dim larger, smaller As Range
Dim foundFlows As Variant


'larger = largest(referenceFlow, findInMonth.Range("G1:G17"))
smaller = smallest(referenceFlow, findInMonth.Range("G1:G17"))
</code>

<code>
Function smallest(ByVal searchValue As Double, searchRange As Range) As Range
Dim searchLocation As Variant
searchLocation = Application.CountIf(searchRange, ">=" & searchValue)
msgbox searchLocation
msgbox searchRange.End(xlDown).Address
msgbox searchRange.End(xlDown).Offset(-searchLocation, 0).Value


If IsError(searchLocation) = False Then
Set smallest = searchRange.End(xlDown).Offset(-searchLocation, 0)
msgbox TypeName(smallest)
Else
msgbox "Error 1 - Data set too small to generate table."
End If
End Function

</code>

 

[RoryA]

This line:

smaller = smallest(referenceFlow, findInMonth.Range("G1:G17"))

should be:

Set smaller = smallest(referenceFlow, findInMonth.Range("G1:G17"))

 

[Jonmo1]

Since the Function smallest is returning a Range Object, then this line
smaller = smallest(referenceFlow, findInMonth.Range("G1:G17"))
should be
Set smaller = smallest(referenceFlow, findInMonth.Range("G1:G17"))


The reason it works in your test Sub, is because in the Test Sub it's not assigning the result of smallest to a variable object, it's just going straight to a message box.

Hope that helps.