countif loop through range

[Sc0tt1e]

I'm having trouble with my variable range for my countif.

I am trying to loop through the range A4:A (bottom row based on Brow). one cell at a time (j) but I am having trouble setting the range for the countif.

Can anyone suggest what I am doing incorrectly so I may recitfy the code?

Please Login or Register  to view this content.

[6StringJazzer]

"I am having trouble" What is the trouble? That is, what do you want to happen, and what is happening instead? If you get a runtime error include the error message.

[nigelog]

Please Login or Register  to view this content.

shoud that be

Please Login or Register  to view this content.

[Sc0tt1e]

Run time error 438
Object doesnt support this property or method

[AB33]

It is worth declaring all variables, so that the code could easilly spot an error

Please Login or Register  to view this content.

[Sc0tt1e]

That still produces the same error

[AB33]

I run a test and did not get any error.

[rorya]

That loop doesn't look right to me. I think you want:

Please Login or Register  to view this content.

unless you really want to skip the first three cells of the range.

[Sc0tt1e]

That loop doesn't look right to me. I think you want:

Please Login or Register  to view this content.

unless you really want to skip the first three cells of the range.

I do, the headings start on row 3, the data starts on row 4

[rorya]

Your range is set to start from row 4 already, but your loop is skipping the first three cells of that range - i.e. rows 4, 5 and 6.

[Sc0tt1e]

I thought that might be happening so was going to test it, that still doesn't explain why I keep getting the error message on the countif row though?

I have no message when I compile the code, only when I run it.

[rorya]

Using what code exactly?

[Sc0tt1e]

Please Login or Register  to view this content.

I've tried researching the error message just not understanding its context in this situation.


Symptoms
Error 438 occurs when running a program in which a form is assigned to a variable and that variable is used to access a control on the form if the program is on a system running Windows 95 with Regional Settings set to a setting other than English (United States). The error text is:

Run-Time Error #438:
Object doesn't support this property or method

Resolution
There are two possible workarounds for this problem.

Access the form directly rather than by a variable containing the form.
-or-
Create property procedures in the form's code to provide access to the properties of the controls on the form.

[rorya]

It's WorksheetFunction.CountIf and not Worksheet.Function.CountIf.

[Sc0tt1e]

It's WorksheetFunction.CountIf and not Worksheet.Function.CountIf.

FFS

Thanks rorya, it's always a fullstop