Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Countif 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Countif 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? 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Countif across sheets?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Countif across sheets?
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. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Countif across sheets?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Countif across sheets?
Thank you!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF, Sorting, on Two Sheets | Excel Discussion (Misc queries) | |||
Countif looking at many sheets | Excel Worksheet Functions | |||
COUNTIF for multiple sheets | Excel Worksheet Functions | |||
Using COUNTIF across different sheets | Excel Discussion (Misc queries) | |||
COUNTIF across sheets | Excel Worksheet Functions |