VBA Countif across sheets?

  • Thread starter Thread starter mlthornton
  • Start date Start date
M

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?
 
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?
Click to expand...

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
 
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:
Click to expand...

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.
 
Hi again,

Am Wed, 31 Dec 2014 09:19:00 +0100 schrieb Claus Busch:
Set wsh = Application.Caller.Parent
Click to expand...

please ignore my previous post. I misunderstood your problem.


Regards
Claus B.
 
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
 
Back
Top