Hello! I'm running into a problem using the the Countif function in
a VBA code. If I type =countapples() into cell A1 of Sheet1, it
returns the expected answer. See countapples() function below:

________________________________________________
Function CountApples()

Dim TopOfRange As Double
Dim BottomOfRange As Double

TopOfRange = 4
BottomOfRange = 10

Set SearchRange = Worksheets("Sheet1").Range(Cells(TopOfRange, 1),
Cells(BottomOfRange, 100))

ReturnCount = Application.WorksheetFunction.CountIf(SearchRange,
"apple")

CountApples = ReturnCount

End Function
________________________________________________


BUT if I type =CountApples() into cell A1 of Sheet2, it returns
#VALUE!

What's the problem with this code? Can CountIf not work across
sheets?

The object ref is not fully qualified to "Sheet1" using 'Cells' as is.
Try...


Public Function CountApples#()
Dim rngSearch As Range

Const dTopRow# = 4: Const dLastRow# = 10

Application.Volatile
With Sheets("Sheet1")
Set rngSearch = .Range(.Cells(dTopRow, 1), .Cells(dLastRow, 100))
End With
CountApples = WorksheetFunction.CountIf(rngSearch, "apple")
Set rngSearch = Nothing
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion