Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
VBA Excel. I have a complicated Countif formula. I want to make a CUSTOM function to simplify user input.?
Here is the working excel defined formula.
=COUNTIF('Time Cards'!E4,">6:00")+COUNTIF('Time Cards'!H4,">6:00")+COUNTIF('Time Cards'!K4,">6:00")+COUNTIF('Time Cards'!N4,">6:00")+COUNTIF('Time Cards'!Q4,">6:00")+COUNTIF('Time Cards'!T4,">6:00")+COUNTIF('Time Cards'!W4,">6:00")
Basically, the cell is greater than 6:00 it counts it.
My Proposed VBA code would include entering the minimum time (6:00 above) and all of the cells to check against.
Here is my code that doesn't work:
Function Counter(Break_Min As Double, dim1 As Double, dim2 As Double, _
dim3 As Double, dim4 As Double, dim5 As Double, dim6 As Double, _
dim7 As Double) As Integer
Counter = WorksheetFunction.CountIf(dim1, ">Break_Min") _
+ WorksheetFunction.CountIf(dim2, ">Break_Min") _
+ WorksheetFunction.CountIf(dim3, ">Break_Min") _
+ WorksheetFunction.CountIf(dim4, ">Break_Min") _
+ WorksheetFunction.CountIf(dim5, ">Break_Min") _
+ WorksheetFunction.CountIf(dim6, ">Break_Min") _
+ WorksheetFunction.CountIf(dim7, ">Break_Min")
End Function
I believe my error fall within my dimensioning or using the second term of the countif statement.
you can dimension the way i did.
It has something to do with range.
dim1 has to be seen as a range of cells.
I also expect that my ">Break_Min" is seen as a text string not as ">6:00" for example
Counter = WorksheetFunction.CountIf(dim1, ">Break_Min")
I should stop asking questions. I always seem to figure it out myself after a couple hours of guess and check.
GOT IT:
VBA code is:
Function Counter(Break_Min As Double, dim1 As Range, dim2 As Range, _
dim3 As Range, dim4 As Range, dim5 As Range, dim6 As Range, _
dim7 As Range) As Integer
Counter = WorksheetFunction.CountIf(dim1, ">" & Break_Min) _
+ WorksheetFunction.CountIf(dim2, ">" & Break_Min) _
+ WorksheetFunction.CountIf(dim3, ">" & Break_Min) .........
Cell formula is =counter("6:00",A1,C1,E1,....,F3)
Works a charm and much cleaner than seven countif statements all sumed up
5 Answers
- CozmosisLv 71 decade agoFavorite Answer
I see you've figured it out while I was typing this up. I just want to show you that you could use a ParamArray instead of multiple Arguments.
Function Counter(Break_Min As Double, ParamArray Nums() As Variant) As Integer
'
Dim N As Integer, cntr As Integer
'
For N = LBound(Nums) To UBound(Nums)
If Nums(N) > Break_Min Then cntr = cntr + 1
Next N
Counter = cntr
End Function
With this, you can use as many cell references as you want e.g.
=COUNTER(TIME(6,0,0), A1, C1, E1, G1, I1, K1, M1, O1, Q1)
- 1 decade ago
One thing that I can see that you are doing wrong is that you are not declaring the variables correctly. When you use the DIM statement, it is telling the compiler/interpreter to read what follows as a variable name.
So, try this instead:
Function Counter(
dim Break_Min As Double,
dim var1 As Double,
dim var2 As Double,
dim var3 As Double,
dim var4 As Double,
dim var5 As Double,
dim var6 As Double,
dim var7 As Double )
dim Counter as integer
Counter = WorksheetFunction.CountIf(var1, ">Break_Min") _
+ WorksheetFunction.CountIf(var2, ">Break_Min") _
+ WorksheetFunction.CountIf(var3, ">Break_Min") _
+ WorksheetFunction.CountIf(var4, ">Break_Min") _
+ WorksheetFunction.CountIf(var5, ">Break_Min") _
+ WorksheetFunction.CountIf(var6, ">Break_Min") _
+ WorksheetFunction.CountIf(var7, ">Break_Min")
End Function
Hope this helps...
Some useful links:
VBA Functions:
http://www.fontstuff.com/VBA/vbatut01.htm
Declaring VBA variables:
http://www.cpearson.com/excel/DeclaringVariables.a...
Is it giving you any errors? Try adding break points and checking what is being stored in the variables. You can look up usage details for the functions and make sure you are passing the correct data/ranges etc.
Nice! Glad you figured it out!
- Anonymous4 years ago
you ought to use Excel purposes by using using right here syntax in VBA, right this is an occasion of the 'Sum' function, an identical utilized to the different outfitted in Excel purposes application.WorksheetFunction.Sum(Rang...
