CODEWITHSUNDEEP
excelvba
VBABeginner
VBABeginner

Reputation: 125

Value in cell to match in a List I have and do it to every cell under that too(Title Not Good)

I would like the macro/code to take the Product that was entered in that cell (relative reference I guess) and scan a list of Products I have. If it doesn't match I would like the message box to tell me which product entered doesn't match with one in my product list and to return its row number where it was entered.

So to give you another example. Lets say we'll enter a number "1" in a cell. I need the macro to search my list of numbers 1-100, if 1 isn't in there I would like it to tell me. Let's say the next number entered (in the following cell under) was a 101. A message box would tell me 101 is not in my number list and its located in row 2.

It probably needs loops. As I will have a range of about 500 cells going down in that column that I would like it to search that product with a list.

Upvotes: 1

Views: 132

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Right click on Sheet Tab --> View Code --> and paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook. The following code assumes that you have a named range called "List" on Sheet2 (code name not the tab name). If you input a value in column A starting from row2, the code will automatically check if the value you entered is found in the named range List and if it doesn't find that value, a msgbox will be popped up to give you some information.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 1 And Target.Row > 1 Then
    If Target <> "" Then
        If Application.CountIf(Sheet2.Range("List"), Target.Value) = 0 Then
            MsgBox "The value " & Target.Value & " you entered in cell " & Target.Address(0, 0) & " was not found in the List", vbExclamation, "Item Not Found!"
        End If
    End If
End If
End Sub

Edit:

Replace the above proposed code with the following code. The following code will be useful if you change multiple cells at once and will provide you a list of all the values which were not found in the List named range.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim str As String
Dim Found As Boolean
str = "The following values were not found in the List." & vbNewLine & vbNewLine
If Target.Column = 1 And Target.Row > 1 Then
    For Each cell In Target
        If cell <> "" Then
            If Application.CountIf(Sheet2.Range("List"), cell.Value) = 0 Then
                Found = True
                str = str & cell.Address(0, 0) & " : " & cell.Value & vbNewLine
            End If
        End If
    Next cell
End If
If Found Then MsgBox str, vbExclamation, "Values Not Found!"
End Sub

Upvotes: 3

Tom Scott
Tom Scott

Reputation: 31

You could use a formula get what you want. 

=IFERROR(MATCH(PRODUCT,LISTofPRODUCTS,0),"Not Found In List")

Same formula using actual cell references:

=IFERROR(MATCH(A1,$B$1:$B$500,0),"Not Found In List")

Where the value you want to look for is in cell A1, and the list you want to look in is B1:B500

To know what row the un-matched value is in, you could use this formula

=IF(ISERROR(MATCH(A1,$B$1:$B$500,0)),"Found","Value in row " & ROW(A1) & " not found")

Upvotes: 0

Related Questions