HELP!! Countif visible cells vba problem

[MI man]

Hello,

The data into Drill worksheet comes from Masterdata worksheet in the same workbook and the data contains 'Risk' as a field. The Risk field includes data of Medium and High Risk for all managers.
I am hiding rows based on the Manager criteria (just like what autofilter does, but I am obliged not to use autofilter here, per instructions from my management).
so, if a person selects Mgr1, the data containing Medium and High Risk for that Mgr1 would populate into the worksheet, and the countif function counts the data based on the criteria, Medium and High:

 Sheets("Drill").Activate
            Range("B1734").Select
            lastcell = Range("B1734").End(xlDown).Row
            If lastcell = 1932 Then
                Range("C1733").Value = 0
                Range("E1733").Value = 0
            Else
                Range("E1733").Value = WorksheetFunction.CountIf(Range("O1735:O1930").SpecialCells(xlCellTypeVisible), "Medium Risk")
                Range("C1733").Value = WorksheetFunction.CountIf(Range("O1735:O1930").SpecialCells(xlCellTypeVisible), "High Risk")
             End If

The Problem:

If you see the code, I have written for when no data is populated, the end row is taken as 1932 (which is the last row+1 for that section), the count is taken as zero, but when data is populated and there is no data in it containing either Medium Risk or High Risk for a particular manager (when the code goes to Else part), then I get an error 1104: No cells found.
How do I overcome this error and make the count as 0?

Your help would be much appreciated.

Thank you.

 

[Isskint]

That is strange.

When i try to replicate your scenario i get an error when 1 or more cells are hidden in the count range BUT i get the correct count otherwise (0 if no high or medium risk).

Where is the code you are running? A code module, Drill worksheet code section, somewhere else?

 

[MI man]

I am sorry, I somehow forgot to mention that I am running it in a code module

 

[MI man]

Hi Isskint,

Thanks for looking into the issue.

I have written this piece of code just now and it seems like it is working good for me as of yet.

Written in code module:

Sheets("Drill").Activate
            Range("B1734").Select
            lastcell = Range("B1734").End(xlDown).Row
            If lastcell = 1932 Then
                Range("C1733").Value = 0
                Range("E1733").Value = 0
            Else
                Dim counter As Integer
                counter = 0
                For i = 1735 To 1930
                    If Cells(i, 1).EntireRow.Hidden = False Then
                        If Cells(i, 15).Value = "Medium Risk" Then
                            counter = counter + 1
'                        Else
'                            counter = 0
                        End If
                    End If
                Next i
                
                Range("E1733").Value = counter
                
                counter = 0
                For i = 1735 To 1930
                    If Cells(i, 1).EntireRow.Hidden = False Then
                        If Cells(i, 15).Value = "High Risk" Then
                            counter = counter + 1
'                        Else
'                            counter = 0
                        End If
                    End If
                Next i
                
                Range("C1733").Value = counter
                
            End If

Need a through testing from my side now.

 

[Brianwarnock]

I'm struggling to understand why the counter is reset to zero when the criteria is not met.

Brian

 

[MI man]

Sorry Brian,
I put the else parts in comments, forgot to delete them while pasting here.