Solved: Check if 2 Values Exist in 2 Columns

[f2e4]

So what I'm looking for is a security measure to add into my existing code.

Example:

Input form:
2 comboboxes (1 for names / 1 for places)

Sheet1:

Stores all the inputs from these two comboboxes

Column A = names
Column B = places


Is anyone aware of any code that will check if a particular combination has already been entered e.g.

Say the following combination has been entered:

A B
John Paris

If this combination has been entered I want the code to display a msgbox saying Value already entered and then Exit Sub.


I have tried various versions of the find function but can't seem to get anything to work.

Any ideas?

[mikerickson]

This example uses input boxes to get the data and then tests if the name/place entered already exists in Sheet2!A1:B100.

Dim uiName as String, uiPlace as String
uiName = Application.InputBox("Enter a Name", Type:=2)
If uiName = "False" Then Exit Sub: Rem cancel pressed
uiPlace = Application.InputBox("Enter a place", Type:=2)
If uiPlace = "False" Then Exit Sub: Rem cancel pressed

If CBool(Evaluate("SUMPRODUCT(--(Sheet2!$A$1:$A$100=" & Chr(34) & uiName & Chr(34) _
                & "),(--(Sheet2!$B$1:$B$100=" & Chr(34) & uiPlace & Chr(34) & ")))")) Then
    MsgBox "Name/Place already exists"
    Exit Sub
Else
    MsgBox "new Name/Place"
End If

[f2e4]

Was using control comboboxes so didn't need the inputbox code at the top

Other than that, it worked a treat.

Thanks a lot mike

[f2e4]

How would i modify the above code to delete the entire row rather than display a msgbox

I thought i could just remove the following:

[VBA] MsgBox "Name/Place already exists"
Exit Sub
Else
MsgBox "new Name/Place"[/VBA]

and replace it with

[VBA]Entirerow.delete[/VBA]

[mikerickson]

The code doesn't identify which row the match is found in. If there is only one line that will have matching Name/Places, this line should remove the matching row.
[VBA]Sheets("Sheet2").Cells(Evaluate("SUMPRODUCT(--(Sheet2!$A$1:$A$100=" & Chr(34) & uiName & Chr(34) _
& "),(--(Sheet2!$B$1:$B$100=" & Chr(34) & uiPlace & Chr(34) & ")),ROW(Sheet2!$A$1:$A$100))"),1).EntireRow.Delete[/VBA]