Filter data based on a another sheet "Filterlist". Need a vlook up back to main sheet to show if value Does not exist.

[Marc L]

Filter solved by a kid VBA beginner with the Macro Recorder, I send him your attachment as a training, I just revamp his code …​

​

 

[Jonnathanjons]

This is my final code integrating both AutoFilter on Selected column and Look up back to the Main Sheet. Thanks for all your support

Sub Macro2()
'
' Macro1 Macro recorded by the Noob Simulator
'
Application.ScreenUpdating = False
On Error Resume Next
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
    
 Const M = "MESSAGE", S = "FilterList"
        'Const M = "MESSAGE"
    With Sheets
        If .Count > 1 Then
           .Item(1).Activate
            If Selection.Column > ActiveSheet.UsedRange.Columns.Count Then Msgbox "Select a header !", 64, M _
                Else ActiveSheet.UsedRange.AutoFilter Selection.Column, Application.Transpose(.Item(2).UsedRange), 7
                   Else
           .add(, .Item(1)).Name = "FilterList"
            Msgbox "Add your search list in column A and proceed!!", 64, M
        End If
        
    End With
    With Sheets(S)
    .[A1].CurrentRegion.Columns(2).Formula = "=IF(ISNUMBER(MATCH(A1," _
                        & ActiveSheet.UsedRange.Columns(Selection.Column).Address(, , , True) & ",0)),""-"",""Not Exist"")"
    .[A1].CurrentRegion.Columns(2).Value = .[A1].CurrentRegion.Columns(2).Value
    .[A1].CurrentRegion.Columns.AutoFit
    End With
Application.ScreenUpdating = True
End Sub

 

[Marc L]

What a corrupted logic in your final procedure ! ​

Maybe the reason why you add an On Error codeline which is totally useless with a smart enough code !​

So hoping this final is used only once 'cause at the second launch it may not work like expected …​

​

 

[Jonnathanjons]

Yes its a corrupted code when I checked later. I can see why. May be due to sheet reference. Do you have any suggestion which works from personal.xlsb file..But Your codes did not include both the requirements
1. Filter the selected column on the Main
2. Look up to the main sheet and and confirm if the code Not Exist.

So these clever code did not work for me. But my poor old long scripts still do the trick. its a little long scripts but still work. Let me know if you have suggestion.

 

[Jonnathanjons]

Ignore my above not. I am able to figure out and fixed the code myself. Its working perfectly as expected. We can close this thread . Thanks for helping me in this project.