VBA Countif across sheets?

[mlthornton]

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?

 

[GS]

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

 

[Claus Busch]

Hi,

Am Tue, 30 Dec 2014 20:28:33 -0800 (PST) schrieb (e-mail address removed):

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:

that you always refer to the activesheet:

Function CountApples() As Long

Dim TopOfRange As Double
Dim BottomOfRange As Double
Dim wsh As Worksheet
Dim SearchRange As Range

TopOfRange = 4
BottomOfRange = 10

Set wsh = Application.Caller.Parent
With wsh
Set SearchRange = .Range(.Cells(TopOfRange, 1), _
.Cells(BottomOfRange, 100))
End With

CountApples = WorksheetFunction.CountIf(SearchRange, "apple")

End Function


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Wed, 31 Dec 2014 09:19:00 +0100 schrieb Claus Busch:

Set wsh = Application.Caller.Parent

please ignore my previous post. I misunderstood your problem.


Regards
Claus B.

 

[GS]

More appropriately, row/col counts are Type Long, as well as the return
from CountIf. Using Type Double suggests you expect a decimal value,but
only whole numbers are used/returned...


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

 

[mlthornton]

Thank you!