PDA

View Full Version : Solved: Check if 2 Values Exist in 2 Columns



f2e4
08-04-2008, 06:15 AM
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
08-04-2008, 06:47 AM
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
08-04-2008, 07:14 AM
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
08-04-2008, 08:29 AM
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:

MsgBox "Name/Place already exists"
Exit Sub
Else
MsgBox "new Name/Place"

and replace it with

Entirerow.delete

mikerickson
08-04-2008, 10:22 AM
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.
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