Count Functions

[Guest]

Alan
I tried to use the macro posted by you. It is not working in Excel 2003.
All the lines are appearing in red. I tried to straighten the line wraps,
still it is not working. Are the "And" and the usage " Application.Countif
"available in Excel 2003 or are they available only 2007 version ? The Help
says worksheet functions should be used by coding them as
Application.Worksheetfunctions.". Kindly guide me.

 

[Guest]

Does anyone know how to assign the value of 1 to a specific word that occurs
throughout my sheet?

 

[JE McGimpsey]

Try this minor modification:

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
Dim sOpLow As String
Dim sOpHi As String
sOp1 = IIf(inclLow, ">=", ">")
sOp2 = IIf(inclHi, ">", ">=")
With Application
CountBetw = .CountIf(iRange, sOpLow & lowNum) - _
.CountIf(iRange, sOpHi & hiNum)
End With
End Function

 

[JE McGimpsey]

If the word exists only once per cell:

=COUNTIF(A1:J100,"*specificword*")

If it may exist multiple times per cell:

=SUMPRODUCT(LEN(A1:J100) - LEN(SUBSTITUTE(A1:J100, "specificword",
"")))/LEN("specificword")

 

[Peo Sjoblom]

Since you are assigning 1 to it you can just count it

=COUNTIF(A:A,"Word")


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

Try this minor modification:

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
Dim sOpLow As String
Dim sOpHi As String
sOp1 = IIf(inclLow, ">=", ">")
sOp2 = IIf(inclHi, ">", ">=")
With Application
CountBetw = .CountIf(iRange, sOpLow & lowNum) - _
.CountIf(iRange, sOpHi & hiNum)
End With
End Function

Or, as a "one-liner"...

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
With Application
CountBetw = .CountIf(iRange, ">" & String(-inclLow, "=") & lowNum) - _
.CountIf(iRange, ">" & String(-inclHi, "=") & hiNum)
End With
End Function


Rick

 

[Alan Beban]

Since all the lines are red, my guess is that you pasted in the >
characters that precede each line of the posting in the newsgroup. If
you simply paste exactly what is not working in a posting in this
thread, that will tell us the problem. Incidentally, the problem is NOT
the Application.WorksheetFunctions discrepancy, which has only limited
ramifications not applicable here.

Alan Beban

 

[Alan Beban]

Try this minor modification:

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
Dim sOpLow As String
Dim sOpHi As String
sOp1 = IIf(inclLow, ">=", ">")
sOp2 = IIf(inclHi, ">", ">=")
With Application
CountBetw = .CountIf(iRange, sOpLow & lowNum) - _
.CountIf(iRange, sOpHi & hiNum)
End With
End Function

That seems to work if you change sOp1 to sOpLow and sOp2 to sOpHi

Alan

 

[Alan Beban]

Or, as a "one-liner"...

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
With Application
CountBetw = .CountIf(iRange, ">" & String(-inclLow, "=") & lowNum) - _
.CountIf(iRange, ">" & String(-inclHi, "=") & hiNum)
End With
End Function


Rick

That doesn't seem to work when the range includes 0 or 15

Alan

 

[Rick Rothstein \(MVP - VB\)]

Or, as a "one-liner"...

That doesn't seem to work when the range includes 0 or 15

Yes, I think I got the inclusion of the equal sign backward on the second
COUNTIF. I think this should work correctly...

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
With Application
CountBetw = .CountIf(iRange, ">" & String(-inclLow, "=") & lowNum) - _
.CountIf(iRange, ">" & String(-Not (inclHi), "=") & hiNum)
End With
End Function

Rick

 

[Alan Beban]

Yes, I think I got the inclusion of the equal sign backward on the
second COUNTIF. I think this should work correctly...

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
With Application
CountBetw = .CountIf(iRange, ">" & String(-inclLow, "=") & lowNum) - _
.CountIf(iRange, ">" & String(-Not (inclHi), "=") & hiNum)
End With
End Function

Rick

Seems to. Thanks to you and JEM for the input.

Alan

 

[JE McGimpsey]

Oops - that's what I get for trying to make it more readable once I put
it in a news article.

Thanks for the correction!

 

[Guest]

Alan
Thanks. It was not the > character. I tried to remove the spaces in the
beginning of each line, all except the following have turned black.

ElseIf inclLow = True And inclHi = False Then
CountBetw = Application.CountIf(iRange, ">=" & lowNum) _
Application.CountIf(iRange, ">=" & hiNum)


The elseif inclLow line is OK. The subsequent two are still in red. I am
not able to correct it.

 

[Guest]

Alan,
Thanks. It was not the > character. I removed the empty spaces in the
beginning of each line, which turned the lines black. One "-" sign was missed
by me while copying, which I have found out and rectified. It's now working.
Thanks once again.

 

[Alan Beban]

Thanks for the feedback. It's always a bit worrisome when something
posted doesn't work for the recipient.

Good luck,
Alan Beban