MsgBox for no results found following filter search

[LSebest1]

Hi,

I have a macro here that pulls a workbook from a specific folder opens it and filters it. What I want is if the filter returns no results (No cells containing "No Comms (2+Days)") then a msg box that reads "No loggers to investigate today". I've tried so many different ways and nothing works. I'm certain there must be a really simple way of doing this that I'm missing. Basically if below the header is empty following "B1" being filtered then the msgbox to appear. Also I'd like the msgbox to appear prior to being copied and pasted. Thanks in advance for any help.

'Open_Selected_folder_via_file_path Workbooks.Open Filename:="V:\SZ\WMAGroups\Wessex Users\CCTs II\Intelligent Infrastructure (Flight Engineers)\REPORTS DL FOLDER\alarm-data*.xlsx" 'Looks_for_loggers_that_havent_comunicated_via_column_filter Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Selection.AutoFilter Selection.AutoFilter ActiveSheet.Range("$A$1:$E$781").AutoFilter Field:=2, Criteria1:= _ "No Comms (2+Days)" 'Auto_sizes_and_selects_via_relative_referencing_then_copies Columns("B:B").ColumnWidth = 55.14 Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy 'Selects_report_workbook_then_pastes_details_from_above_macro Windows("Reports Workbook.xlsm").Activate Range("B22").Select ActiveSheet.Paste Range("B22").Select 'Closes_Selected_workbook_and_supress_save_prompts Dim wb As Workbook Application.DisplayAlerts = False For Each wb In Application.Workbooks If wb.Name Like "alarm-data*" Then wb.Close Next wb Range("B2").Select End Sub

 

[fadee2]

Hi LSebest1,

Welcome to the board.

The code available here, should help you out.

 

[LSebest1]

Hi fadee2, I have a few days off so will give this a try once I'm back in the office. Thanks for the reply andI'll let you know if this has worked

 

[fadee2]

Hi fadee2, I have a few days off so will give this a try once I'm back in the office. Thanks for the reply andI'll let you know if this has worked

You are welcome, credit goes to @venkat1926 for the answer...
Sure, do let us know...

 

[LSebest1]

You are welcome, credit goes to @venkat1926 for the answer...
Sure, do let us know...

Hi fadee2, that worked perfect (In the end with a little tweaking, newbie issues lol)

Also thanks to @venkat1926 for the original post

 

[fadee2]

i m glad it worked.
care to share your findings and resulting code on the board, i m sure it will help a lot of learners like me... ?

 

[LSebest1]

i m glad it worked.
care to share your findings and resulting code on the board, i m sure it will help a lot of learners like me... ?

Sure - Changed the range to the ones required, changed the count to countA (as it was a text search) And changed it from Integer to string.

'Returns_Msgbox_If_No_Results
Dim r As Range, j As String
Set r = Range(Range("b2"), Range("b2").End(xlDown))
r.AutoFilter field:=1, Criteria1:="No Comms (2+Days)"
j = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
'MsgBox j
If j = 0 Then
MsgBox "There are no Centrix Loggers to investigate"
End If
ActiveSheet.AutoFilterMode = False

I now have 4 other tabs that require the same result so will test this macro on them (Although once again these will require tweaking)

 

[fadee2]

thanks for the code.???