if condition ignoring else-CodePudding

I am trying to find blank cells in column "Issuer", and if there are blank cells, it should show rows with blank cells and a message box indicating there are blank cells however if there no blank cells, nothing should happen(no pop up message).

I have below code which is doing what's intended to do, however the pop-up message always appears even if there is no blanks.

Else statement is being ignored. Really appreciate your help.

Summary of the code:

1) filter for all asset classes except cash in column 8 2) filter on column "Issuer" for blanks, if there are blank fields - message should pop-up that there are blanks if there are no blank fields, reset the filter and continue

Sub Filter()

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
Dim ws As Object

ActiveSheet.Range("H:H").AutoFilter Field:=8, Criteria1:="<>Cash", Operator:=xlAnd

SearchCol = "Issuer"

For Each ws In Worksheets
    Set rng1 = ws.UsedRange.Find(SearchCol, , xlValues, xlWhole)
    With ws.Range("A2")
        If .AutoFilter(Field:=rng1.Column, Criteria1:="") Then
            MsgBox ("Blank Coupan Rate")
        Else
            On Error Resume Next
        End If
    End With
Next ws
End Sub

CodePudding user response：

It's hard to tell exactly what you're going for with your filter, and what you're defining as a blank cell. Excel's definition of a blank cell has an empty value (null) which would mean any value with ="" or 0 would not be considered blank.

Assuming that's what you're also defining as blank as Excel, then you can use the built in worksheet function countBlank test for blanks. The challenge I'm guessing you are dealing with is testing the proper range (you seem to be using a condition tied to filter which doesn't seem correcT)

In the below example you can see a test macro that searches in column H for blanks and displays. You could probably leverage something like this with what you have to test what range you're using or where the issue is.

Sub exampleTHis()

Dim ws As Worksheet, testRange As Range, aCount As Long, zAnswer

For Each ws In ThisWorkbook.Worksheets

    Set testRange = Intersect(ws.Range("H:H"), ws.UsedRange)

    If Not testRange Is Nothing Then
        
        aCount = Application.WorksheetFunction.CountBlank(testRange)
        If aCount > 0 Then
         'blank cells found....
            zAnswer = MsgBox(aCount & " blank values found in at " & ws.Name & testRange.Address & ".  Continue macro?", vbYesNo)
            
            If zAnswer = vbNo Then Exit For
            
        End If
    End If
    
Next ws

End Sub

Excel does have a countblank function which could easily be used to test your range of data, the only question is if