• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CountIf reports an error

S

sakthi santhanam

New Member
Hi here I have added the code where I autofilter a column and based on that count the value. The countif works for the first value and but for the second time after the autofilter it throws an error as "Unable to get the countif property of the worksheetfunction class.

Code: 
For Each a In xlws11.Range("F2:F" & xlws11.UsedRange.Rows.Count) ' acc wise

  If Not IsEmpty(a.Value) Then
  UserForm9.ListBox1.AddItem (a.Value)

If UserForm9.ComboBox1.Value = "Alert" Then

lw = xlws2.Rows.Count

xlws2.UsedRange.AutoFilter Field:=9, Criteria1:=a.Value

Set cell1 = xlws2.Range("K3:K" & lw).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants)
[COLOR=#ff0000]l1 = Application.WorksheetFunction.CountIf(cell1, "No") 'Error at this line[/COLOR]
xlws2.autofiltermode = false 
end if
end if
next a

Here for the first value in the list box the filter happens correctly and gets the count of the range based on the criteria. But for the second value in the listbox it filters as per the value but when it comes to the l1 it shows the error. If criteria is not satisfied then it should return 0 that I get for the first run but for the second run it shows the error.

Regards,
sakthi santhanam
 
Hi Santhanam ,

The problem might be the the line :

Set cell1 = xlws2.Range("K3:K" & lw).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants)

If nothing is found , then cell1 is Nothing. You should check this with an IF statement as follows :

If Not (cell1 Is Nothing) then
.......execute the COUNTIF function
EndIf

Narayan
 
Hi Narayan,

Thanks for your immediate response. In the range i have mentioned K3 till the used range. but for my second value that pass from the listbox for filtering the data the range starts with K8 and with different rows as per the filtered data.Correct me if i am wrong here.... Where the range is specified from K3 but the column starts with K8 after filtering so countif will assume K3 till K7 as null which is throwing the error?

Regards,
sakthi santhanam
 
Hi Santhanam ,

Can you put a breakpoint at the following line ?

l1 = Application.WorksheetFunction.CountIf(cell1, "No")

On each run through the loop , when execution halts at that statement , in the Immediate window , type in :

?cell1.Address

and see what is displayed.

Narayan
 
Hi Narayan,

In immediate window the cell1.address is as such $K$3:$K$7 which is not throwing any error, but for the second value from the listbox the immediate window shows the address as $K$8:$K$12,$K$17:$K$32 this is the ?cell1.address which is throwing "unable to get countif property of the worksheetfunction class"

Regards,
sakthi
 
Hi,
can any one help me with this issue. The countif property accepts continuous rows of data for validation.. but in case there is a split in the row and the countif property is with different rows which is not continuous countif property divides the range into smaller continuous range which is an error as per countif property.

what else has to be done?

Regards
sakthi
 
Please upload the sample file..
without structure of the file.. we can just only through some blind shot with lost of imagination..
 
Hi ,

You have already understood the issue ; the solution is somewhat complicated. If you just want an answer to the COUNTIF issue , check out these links , and see if they help :

http://stackoverflow.com/questions/8421139/how-to-define-a-non-continuous-range-in-countif

http://stackoverflow.com/questions/11065078/excel-count-same-number-values-in-noncontiguous-range

If not , let us see if we can solve the underlying problem using some other approach ; for this , you need to explain what exactly you want done.

Narayan
 
Hi,

The sample file is uploaded. Here what happens is autofilter the column A and B separately and find the count of "No" in C column. So I autofilter the column B using the values which i pass in a for loop to get the entire count. the same I do with the column A as I disable the autofilter on column B and enable it on column A and try to get the count of "No" from the column C as per the autofilter criteria. But it throws an error when I work with column A as it gets the range as a split range.

The moto is to get the count of no of "No" in column C as per the filter made.

have some problem in uploading the sample file...

Regards
sakthi
 
Hi Sakthi!

* First of all..
lw = xlws2.Rows.Count

I dont know what this XLWS2 is , worksheet of range with the used range.. Use some logic to set your LW.

If i assume. you have enough knowledge to set LW, and overlooking all other code part.. then
try this!..

Code: 
Sub test()
lw = 50
Dim str As String
  MsgBox Evaluate("=SUMPRODUCT(SUBTOTAL(3,OFFSET(K3:K" & lw & ",ROW(K3:K" & lw & ")-MIN(ROW(K3:K" & lw & ")),,1))*(K3:K" & lw & "=""No""))")
End Sub
 
and in other way.. if you are facing issue to apply it..
try this..

Code: 
For Each a In xlws11.Range("F2:F" & xlws11.UsedRange.Rows.Count) ' acc wise

  If Not IsEmpty(a.Value) Then
  UserForm9.ListBox1.AddItem (a.Value)

If UserForm9.ComboBox1.Value = "Alert" Then

lw = xlws2.Rows.Count

xlws2.UsedRange.AutoFilter Field:=9, Criteria1:=a.Value

Set cell1 = xlws2.Range("K3:K" & lw).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants)
For Each cel In cell1
  If cel.Value = "No" Then l1 = l1 + 1
Next cel
xlws2.AutoFilterMode = False
MsgBox l1
End If
End If
Next a
 
Hi Deb,

Thanks for your help and the issue is with the cell1... after the autofilter the data as per the criteria is with different rows where they are not in continuous sequence... Ex. A5,A6,A7,A8,A9,A15,A16,A17,A18,A32,A39,A40.... this is what the sequence so what happens when setting the break point at that line and checked with the immediate window using ?cell1.address which was assisted by Mr.Narayan... I understood that the row was not selected one together rather it has been captured as like this cell1 = $A$5:$A$9,$A$15:$A$18,$A$32:$A$32,$A$39:$A$40... this is not a correct property on countif...

For this I have found a solution where before doing performing autofilter I have sorted that column in ascending/descending order which made the data to be in a continuous sequence...

Code: 
xlws2.Range("A3:Z" & xlws2.UsedRange.Rows.Count).Sort Key1:=xlws2.Range("G3"), Order1:=xlAscending

xlws2.UsedRange.AutoFilter Field:=7, Criteria1:=a.Value

this works for me perfectly...

xlws2 is the defined as worksheet...and lw is the for refer the used range count.

Regards,
sakthi
 
You must log in or register to reply here.
Back
Top