Thread: Macro to filter content and show not found info

Dear all,

First of all thanks for taking the time to help me with this project.

The situation is as follows:

I have a big spreadsheet with lots of data. Specifically in the first column I have a string I use to filter the information and make changes. This is a unique number in the column, there aren't duplicated.

The idea was to make a macro that will ask for those numbers and once entered it will automatically filter the content. Searching the NET I've found some pointers and came out with something "usefull":

--------------------------------------------------------------------------

Sub Macro()

Dim x As String
Dim arCriteria(1 To 100) As String

On Error GoTo Canceled

'Asks for number of lines to filter
x = InputBox("Enter amount of data to filter")

For i = 1 To x

    On Error GoTo Canceled
    
    'This is the actula number I want to filter
    arCriteria(i) = InputBox("Input number")
            
Next

    'Goes into the for and populates the array, once all info is entered it uses that array to filter the information
    ActiveSheet.Range("$A$1:$Z$2000").AutoFilter Field:=2, Criteria1:=arCriteria, Operator:= _
       xlFilterValues
       
Canceled:
End Sub

--------------------------------------------------------------------------

All this works perfectly and as desired.

Now I want to add some extra function: If the number I input isn't found in the column or spreadsheet I would like to add it to some kind of array or similar and after the filter is applied, a Msgbox pops out saying something like:

"Following numbers wasn't found:
xxxxxxxx1
xxxxxxxx2
xxxxxxxx3"

Is this possible with the code I already have? Could someone point me in the right direction?

Thank you very much for the help.

Regards!

Please edit your post to use code tags in accordance with forum rules. We may not assist you until you do.
Type:
[code]all your code here[/code]