Hi,

I have the following function

Function cohort(stap As Double) As Double
cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<stap+5") - Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<stap")
End Function

The problem is that the function does not recognise "stap" in the function.
If i replace stap with a number then the function works. but i need the
number to be a variable..

some additional info
on sheet "deelnemers" there is a list of people with ages in column D
stap is the age of the people. So for example i want to know the number of
people between 30(stap + 5) and 25(stap)

I've been puzzling for ours but no result other then i know wher the function
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com

"<"&stap&"+5"

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi,

I have the following function

Function cohort(stap As Double) As Double
cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<stap+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<stap")
End Function

The problem is that the function does not recognise "stap" in the
function.
If i replace stap with a number then the function works. but i need the
number to be a variable..

some additional info
on sheet "deelnemers" there is a list of people with ages in column D
stap is the age of the people. So for example i want to know the number of
people between 30(stap + 5) and 25(stap)

I've been puzzling for ours but no result other then i know wher the
function
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com

Hi Niek,

your suggestion doesn't work....

this is my new function:

cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<"&stap&"+5") - Application.CountIf(Worksheets("deelnemers").Range ("D3:D992")
, "<stap")

I get a "compile error" in your suggestion

I adapted your suggestion to:

cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"), "<" &
stap & "+5") - Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" & stap)

This generates no error message but now it seems that it generates the number
of items in the list that do not meet the criteria instead of those that do...

with step = 25 i get a respons of 927 while there ar only a few people
between 25-30

any ideas?

I get the message:
Niek Otten wrote:
"<"&stap&"+5"

Hi,

[quoted text clipped - 21 lines]
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com

Jean-Pierre,

This works for me

With ActiveSheet 'Worksheets("deelnemers")
cohort = Application.CountIf(.Range("D3:D992"), "<" & stap + 5) - _
Application.CountIf(.Range("D3:D992"), "< " & stap)
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi Niek,

your suggestion doesn't work....

this is my new function:

cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<"&stap&"+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992")
, "<stap")

I get a "compile error" in your suggestion

I adapted your suggestion to:

cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" &
stap & "+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" & stap)

This generates no error message but now it seems that it generates the
number
of items in the list that do not meet the criteria instead of those that
do...

with step = 25 i get a respons of 927 while there ar only a few people
between 25-30

any ideas?

I get the message:
Niek Otten wrote:
"<"&stap&"+5"

Hi,

[quoted text clipped - 21 lines]
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com

Bob,

I'm interested in an other aspect of this topic: How can one use CountIf
worksheet function without the WorksheetFunction. qualifier?
In the VB Help topic "Using Microsoft Excel Worksheet Functions in Visual
Basic" we are explicitly told that "In Visual Basic, the Microsoft Excel
worksheet functions are available through the WorksheetFunction object.", and
also the examples consistently use the WorksheetFunction. qualifier.
What is the truth?
Regards,
Stefi




€žBob Phillips€� ezt Ã*rta:

Jean-Pierre,

This works for me

With ActiveSheet 'Worksheets("deelnemers")
cohort = Application.CountIf(.Range("D3:D992"), "<" & stap + 5) - _
Application.CountIf(.Range("D3:D992"), "< " & stap)
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi Niek,

your suggestion doesn't work....

this is my new function:

cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<"&stap&"+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992")
, "<stap")

I get a "compile error" in your suggestion

I adapted your suggestion to:

cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" &
stap & "+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" & stap)

This generates no error message but now it seems that it generates the
number
of items in the list that do not meet the criteria instead of those that
do...

with step = 25 i get a respons of 927 while there ar only a few people
between 25-30

any ideas?

I get the message:
Niek Otten wrote:
"<"&stap&"+5"

Hi,

[quoted text clipped - 21 lines]
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com

Stefi,

The whole truth, or just part of the truth?

Both methods (Application and WorksheetFunction) will work. Application was
the pre Excel 97 way of doing it and is retained for compatibility, but
there are some nuances in the way they work.

There were problems with some functions in Excel97 and 2000 using the
WorksheetFunction method, which did not surface using Application .

WorksheetFunction (and Application.WorksheetFunction) supports the "auto
list members" option, whereas Application alone does not.
In Excel 97 it was necessary to use Application.WorksheetFunction to get
this auto list, but in later versions WorksheetFunction alone works.

Not all worksheet functions are exposed to VBA. Functions not exposed by the
WorksheetFunction object usually have a
VBA equivalent (e.g., Left, Year), but be aware, they do not necessarily
work in exactly the same way..

Functions within Add-ins, such as the Analysis Toolpak, cannot be called
with Application or WorksheetFunction.
Errors are handled differently. When a function is called directly using
Application (e.g., Application.VLookup) , the result is a variant containing
an error. When called directly using WorksheetFunction (e.g.,
WorksheetFunction .*VLookup), the function will raise an error. Both can be
managed, but in different ways

Dim ans As String

On Error Resume Next
ans = WorksheetFunction.VLookup("value", table_array, 2, False)
On Error GoTo 0
If ans = "" Then
MsgBox "not found using WorksheetFunction"
End If


If IsError(Application.VLookup("value", table_array, 2, False)) Then
MsgBox "not found using Application"
End If


WorksheetFunction is faster, by an order of circa 20%.


--

HTH


RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Bob,

I'm interested in an other aspect of this topic: How can one use CountIf
worksheet function without the WorksheetFunction. qualifier?
In the VB Help topic "Using Microsoft Excel Worksheet Functions in Visual
Basic" we are explicitly told that "In Visual Basic, the Microsoft Excel
worksheet functions are available through the WorksheetFunction object.",
and
also the examples consistently use the WorksheetFunction. qualifier.
What is the truth?
Regards,
Stefi




"Bob Phillips" ezt írta:

Jean-Pierre,

This works for me

With ActiveSheet 'Worksheets("deelnemers")
cohort = Application.CountIf(.Range("D3:D992"), "<" & stap + 5) - _
Application.CountIf(.Range("D3:D992"), "< " & stap)
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi Niek,

your suggestion doesn't work....

this is my new function:

cohort =
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<"&stap&"+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992")
, "<stap")

I get a "compile error" in your suggestion

I adapted your suggestion to:

cohort =
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" &
stap & "+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" & stap)

This generates no error message but now it seems that it generates the
number
of items in the list that do not meet the criteria instead of those
that
do...

with step = 25 i get a respons of 927 while there ar only a few people
between 25-30

any ideas?

I get the message:
Niek Otten wrote:
"<"&stap&"+5"

Hi,

[quoted text clipped - 21 lines]
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com

Hi Nick,

I have got the solution:

cohort = Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"), "<" &
stap + 5) - Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" & stap)

Thanks anyway.

Niek Otten wrote:
"<"&stap&"+5"

Hi,

[quoted text clipped - 21 lines]
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com