| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
function malfunctioning using countif and a variable
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
"<"&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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
Thanks, Bob, this is a really exhausting explanation. Some of it should be
involved in the Help text similarly to some other references to backward compatibility. I'll use WorksheetFunction method in the future (just like in the past) but being aware of going on the right way. Stefi €žBob Phillips€ ezt Ã*rta: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
Hi Stefi,
Part of what I was trying to say is that there is no right way IMO, you just have to know how both work, as with all things, so that you can use them to your benefit, you are in control of it, not vice versa. I personally prefer the Application way as the error handling is simpler and more intuitive, again IMO -- HTH RP (remove nothere from the email address if mailing direct) "Stefi" wrote in message ... Thanks, Bob, this is a really exhausting explanation. Some of it should be involved in the Help text similarly to some other references to backward compatibility. I'll use WorksheetFunction method in the future (just like in the past) but being aware of going on the right way. Stefi "Bob Phillips" ezt írta: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
Hi Bob,
I see your point, I was confused by "backward compatibility", I supposed that a new method is replaced by an old one because the new one is better. It seems now that in some aspects it's better (e.g. speed), in some other aspects it isn't (e.g. error handling). Sorry for disturbing you again with my linguistic problem, but I never heard the abbreviation IMO. Please tell me what does it stand for! Regards, Stefi €žBob Phillips€ ezt Ã*rta: Hi Stefi, Part of what I was trying to say is that there is no right way IMO, you just have to know how both work, as with all things, so that you can use them to your benefit, you are in control of it, not vice versa. I personally prefer the Application way as the error handling is simpler and more intuitive, again IMO -- HTH RP (remove nothere from the email address if mailing direct) "Stefi" wrote in message ... Thanks, Bob, this is a really exhausting explanation. Some of it should be involved in the Help text similarly to some other references to backward compatibility. I'll use WorksheetFunction method in the future (just like in the past) but being aware of going on the right way. Stefi "Bob Phillips" ezt Ã*rta: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
IMO - In My Opinion
See http://www.ucc.ie/cgi-bin/uncgi/acronym Regards Bob "Stefi" wrote in message ... Hi Bob, I see your point, I was confused by "backward compatibility", I supposed that a new method is replaced by an old one because the new one is better. It seems now that in some aspects it's better (e.g. speed), in some other aspects it isn't (e.g. error handling). Sorry for disturbing you again with my linguistic problem, but I never heard the abbreviation IMO. Please tell me what does it stand for! Regards, Stefi "Bob Phillips" ezt írta: Hi Stefi, Part of what I was trying to say is that there is no right way IMO, you just have to know how both work, as with all things, so that you can use them to your benefit, you are in control of it, not vice versa. I personally prefer the Application way as the error handling is simpler and more intuitive, again IMO -- HTH RP (remove nothere from the email address if mailing direct) "Stefi" wrote in message ... Thanks, Bob, this is a really exhausting explanation. Some of it should be involved in the Help text similarly to some other references to backward compatibility. I'll use WorksheetFunction method in the future (just like in the past) but being aware of going on the right way. Stefi "Bob Phillips" ezt írta: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function malfunctioning using countif and a variable
Good idea from the Bible: Instead of giving the starving man a fish, teach
him to catch a fish! (My translation, not original citation) Thanks, Stefi €žBob Phillips€ ezt Ã*rta: IMO - In My Opinion See http://www.ucc.ie/cgi-bin/uncgi/acronym Regards Bob "Stefi" wrote in message ... Hi Bob, I see your point, I was confused by "backward compatibility", I supposed that a new method is replaced by an old one because the new one is better. It seems now that in some aspects it's better (e.g. speed), in some other aspects it isn't (e.g. error handling). Sorry for disturbing you again with my linguistic problem, but I never heard the abbreviation IMO. Please tell me what does it stand for! Regards, Stefi "Bob Phillips" ezt Ã*rta: Hi Stefi, Part of what I was trying to say is that there is no right way IMO, you just have to know how both work, as with all things, so that you can use them to your benefit, you are in control of it, not vice versa. I personally prefer the Application way as the error handling is simpler and more intuitive, again IMO -- HTH RP (remove nothere from the email address if mailing direct) "Stefi" wrote in message ... Thanks, Bob, this is a really exhausting explanation. Some of it should be involved in the Help text similarly to some other references to backward compatibility. I'll use WorksheetFunction method in the future (just like in the past) but being aware of going on the right way. Stefi "Bob Phillips" ezt Ã*rta: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel lock / Notify feature malfunctioning | Excel Worksheet Functions | |||
| Automatic value is malfunctioning | Excel Worksheet Functions | |||
| How to use VBA variable in COUNTIF function? | Excel Worksheet Functions | |||
| Sorting malfunctioning | New Users to Excel | |||
| Fitter malfunctioning? | Charts and Charting in Excel | |||
Linear Mode
