MsgBox for no results found following filter search

LSebest1

New Member
Joined
Dec 7, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
341
Office Version
  1. 2019
Platform
  1. Windows
Hi LSebest1,

Welcome to the board.

The code available here, should help you out.
 

LSebest1

New Member
Joined
Dec 7, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Nov 7, 2020
Messages
341
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 7, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 7, 2020
Messages
341
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 7, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Nov 7, 2020
Messages
341
Office Version
  1. 2019
Platform
  1. Windows
thanks for the code.👍👍👍
 

Watch MrExcel Video

Forum statistics

Threads
1,132,642
Messages
5,654,547
Members
418,139
Latest member
nimesh72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top