Filter when name not found goes on a loop

[Patriot2879]

Hi good morning, I have the code that works great apart when the name cant filter on the name I M2, it goes on a loop about times, until it finally stops and highlights empty fields, is there anyway t stop the loop and return nothing straight away instead?

Private Sub dofilter()
    Sheets("Filter").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    
    Sheets("Work Issue").Select
    Range("C:C").AutoFilter Field:=3, Criteria1:=Range("M2").Value
    Range("A1").Select
    
    ActiveCell.Offset(1).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Sheets("Filter").Select
    ActiveSheet.Paste Destination:=Range("C10")
     
    Columns.AutoFit
    Range("B2").Select
End Sub

 

[Patriot2879]

Hello, I think I have to do an error handling code if no value is found, but I don't know how to do that, please can you advise?

 

[Trevor G]

Check the cell first to see if it is empty and if so then show message box and stop the process if not then complete the process. Hope this works for you!

Sub checkEmpty()
'Check if "Work Issue" has content before proceeding
'If empty show message and stop the process
If IsEmpty(Sheets("Work Issue").Range("M2")) = True Then
MsgBox "M2 is empty do you want to continue", vbOKOnly
Else: Sheets("Filter").Select
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Sheets("Work Issue").Select
Range("C:C").AutoFilter Field:=3, Criteria1:=Range("M2").Value
Range("A1").Select

ActiveCell.Offset(1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Filter").Select
ActiveSheet.Paste Destination:=Range("C10")

Columns.AutoFit
Range("B2").Select
End If
End Sub

 

[dmt32]

Hello, I think I have to do an error handling code if no value is found, but I don't know how to do that, please can you advise?

Hi,
untested but see if this update to your code goes in right direction

Sub Dofilter()
    Dim rng As Range
    Dim FilterRange As Long
    Dim wsFilter As Worksheet
    Dim FilterValue As String
    
        Set wsFilter = Worksheets("Filter")
        
        wsFilter.Range("B2").CurrentRegion.Clear
    
        With Sheets("Work Issue")
        FilterValue = .Range("M2").Value
            .UsedRange.AutoFilter Field:=3, Criteria1:=FilterValue
        Set rng = .AutoFilter.Range
    End With
            
'count filter matches
        FilterRange = rng.Columns(3).SpecialCells(xlCellTypeVisible).Count - 1
    
        If FilterRange > 0 Then
'un rem if to remove header row
           'Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
'copy to filter sheet
            rng.SpecialCells(xlCellTypeVisible).Copy wsFilter.Range("C10")
        Else
'inform user
            MsgBox FilterValue & Chr(10) & "Filter Value Not Found", 48, "Not Found"
        End If
'clear filter
    rng.AutoFilter
End Sub

adjust as required.

Hope Helpful

Dave

 

[Patriot2879]

thank you Dave I shall test it out

 

[Patriot2879]

HI Dave, good morning, thank you for your help, I have tried the new code, but it still goes through a loop. Hope you can still help please?

 

[dmt32]

HI Dave, good morning, thank you for your help, I have tried the new code, but it still goes through a loop. Hope you can still help please?

Hi,
not sure what you mean goes through a loop - there is no loop in my code.

Can only suggest that you place a copy of your workbook with sample data in a dropbox & place a link to it here. Maybe one of us here will be able to assist further.


Dave

 

[Patriot2879]

https://www.dropbox.com/s/eifeod62w1bik28/WhandoverMREXCEL.xlsx (004).xlsm?dl=0

Hi Dave, thank you for getting back to me, I have attached the file into dropbox please see link below, if you click on the button called Email then on the dropdown box click on Alan Moore this is when it goes into a loop.

 

[dmt32]

https://www.dropbox.com/s/eifeod62w1bik28/WhandoverMREXCEL.xlsx (004).xlsm?dl=0

Hi Dave, thank you for getting back to me, I have attached the file into dropbox please see link below, if you click on the button called Email then on the dropdown box click on Alan Moore this is when it goes into a loop.

Had a quick look at your file - some observations

- you have a duplicate of procedure dofilter in modules 1 & 2 - best to only have one

- cannot see code I posted in your project or how dofilter is called in your form?

- you appear to have posted what looks like real personal data in public forum. If this is real data, then suggest good idea to remove it as may be in conflict with data protection rules.


Dave

 

[Patriot2879]

Hi Dave, now removed, thankyou for that.

Sorry I didn't understand exactly where to put you code? if you can advise what to do please, I am still learning. thanks for the help