Search for Data already Exists

[annadinesh]

Dear Experts

i have 2 sheets
1 Data Entry
2 Database

in Database there are 4 coloums Name Dealer Code, Claim Number, Claim Date and Claim Amount
in Data Entry there is 2 Cells with heading Dealer Name and Claim Number and a command Button Name "Add to Database"

I want if i enter data on both Cells and while Save it should search both value in Sheet Database on Same Row if Dealer Name and Claim Number found already exists on Same Row then a Msg Shows Dealer Name and Claim Number already Exists.


Thank you

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also be helpful to have a small set of sample data for both sheets with XL2BB so that we can copy for testing (as well as see just what you have and where it is) and explain your requirement with reference to the sample data.

 

[Skyybot]

Which two cells are used on Data Entry sheet?

 

[annadinesh]

Which two cells are used on Data Entry sheet?

A1 - Dealer Code
B1 - Claim Number

 

[Skyybot]

Test this on a copy of your Workbook as unexpected results may occur and Macro actions can't be undone. Paste this in the same Sub with your Button before any other code.

Dim wb As Workbook, dSht As Worksheet, eSht As Worksheet, cNum, dName, lRow As Long, fRng As Range, lRng As Range
Set wb = ThisWorkbook: Set dSht = wb.Sheets("Database"): Set eSht = wb.Sheets("Data Entry")
lRow = dSht.Columns("B").Rows(dSht.Rows.Count).End(xlUp).Row
Set fRng = dSht.Range("B2:B" & lRow)
dName = eSht.Range("A1"): cNum = eSht.Range("B1")
Set lRng = fRng.Find(cNum.Value, LookAt:=xlWhole)
If Not lRng = "" Then
    If lRng.Offset.Value = dName.Value Then
        MsgBox "Claim already exists.", vbInformation, "Duplicate Claim"
    Exit Sub
    End If
End If

 

[Peter_SSs]

@annadinesh
If your question is not resolved by the above code, please review post #2
In any case, please review the first paragraph of that post. It is your interests for helpers to have information that best lets them help you.

 

[annadinesh]

Test this on a copy of your Workbook as unexpected results may occur and Macro actions can't be undone. Paste this in the same Sub with your Button before any other code.

Dim wb As Workbook, dSht As Worksheet, eSht As Worksheet, cNum, dName, lRow As Long, fRng As Range, lRng As Range
Set wb = ThisWorkbook: Set dSht = wb.Sheets("Database"): Set eSht = wb.Sheets("Data Entry")
lRow = dSht.Columns("B").Rows(dSht.Rows.Count).End(xlUp).Row
Set fRng = dSht.Range("B2:B" & lRow)
dName = eSht.Range("A1"): cNum = eSht.Range("B1")
Set lRng = fRng.Find(cNum.Value, LookAt:=xlWhole)
If Not lRng = "" Then
    If lRng.Offset.Value = dName.Value Then
        MsgBox "Claim already exists.", vbInformation, "Duplicate Claim"
    Exit Sub
    End If
End If

ERROR

 

[Peter_SSs]

ERROR

So ..

@annadinesh
If your question is not resolved by the above code, please review post #2

 

[annadinesh]

Book1.xlsm
	A	B	C	D	E	F	G	H
1	E1202	a003
2
3
4
Data Entry

Book1.xlsm
	A	B	C	D	E
1	DEALER CODE	CLAIM NUMBER	CLAIM DATE	CLAIM AMOUNT
2	E1202	a003	27.11.2023	66
3	E3406	a002	27.11.2023	55
4	E1202	a003	27.11.2023	66
5	E1207	a001	27.11.2023	70
6	E1202	a001	27.11.2023	62
7	E3406	a003	27.11.2023	333
8	E1202	a005	27.11.2023	36
9	E1207	a003	27.11.2023	693
10	E1202	a003	27.11.2023	33
11	E3406
Database

IF same data - Claim Number with Same Dealer Code found then it should show an msg

 

[Peter_SSs]

Thanks for the XL2BB sample data
But we still did not get your Excel version listed in your profile.

Do you really need vba? Could you just use a formula something like this?

annadinesh.xlsm
	A	B	C
1	E1202	a003	Dealer Name and Claim Number already Exists.
Data Entry

Cell Formulas
Range	Formula
C1	C1	=IF(COUNTIFS(Database!A$1:A$1000,A1,Database!B$1:B$1000,B1),"Dealer Name and Claim Number already Exists.","")