Check row for multiple values from Excel
I am making a data entry form through VBA excel. I am very new at this and was unable to get any meaningful search results as I was unsure of the name of what I was trying to do.
Anyway, the form I am making is for users to enter data collected from running audits and when "Save" button is clicked, the data from the text boxes and combo boxes is written to a new row in an excel workbook. In my form there are four fields that identify each data entry (Year, Quarter, Day, Ward) followed by the rest of the data. I am trying to get some code to first check if there is a row with the same values already in the workbook to prevent duplicate entries.
e.g. The spreadsheet contains the following data - YEAR -- QUARTER---- DAY--- WARD
2013----------1--------- Monday -- ICU 2013----------2--------- Monday -- ICU 2013----------3--------- Monday -- ICU 2013----------4--------- Monday -- ICU 2014----------1--------- Monday -- ICU
And a user enters a new entry with the following values
Year - 2014 Quarter - 1 Day - Monday Ward - ICU
I need some code that would give a result (Msgbox saying duplicate entry etc)
but if they entered one that was
Year - 2014 Quarter - 2 Day - Monday Ward - ICU
All is ok and they are able to click "save"
I appreciate your help (sorry for the dumb question!)
Here's a code fragment (Function IsDuplicate()) which loops through a range until the first blank row (to be precise to the first row where the fist column is empty) and compares 4 key fields with 4 parameters you are providing.
You can call this from your Save button and, depending on the outcome, proceed with writing and closing the form or refuse the writing and display a MsgBox.
Sub Test() Dim DTab As Range Set DTab = ActiveSheet.[B3] ' define start of data table If IsDuplicate(DTab, 1, 1, 1, 1) Then ' your form input here instead of 1,1,1,1 MsgBox ("duplicate") ' stay in form Else MsgBox ("unique") ' write record and close form End If End Sub Private Function IsDuplicate(DRange As Range, P1 As Variant, P2 As Variant, P3 As Variant, P4 As Variant) As Boolean Dim Idx As Long IsDuplicate = False Idx = 2 ' 1st row is header Do While DRange(Idx, 1) <> "" ' break at 1st blank row If DRange(Idx, 1) = P1 And _ DRange(Idx, 2) = P2 And _ DRange(Idx, 3) = P3 And _ DRange(Idx, 4) = P4 Then IsDuplicate = True Exit Do End If Idx = Idx + 1 Loop End Function
You will need this function quite often .... sometimes you want to return Idx instead of just a True/False to return the number of the record - if you must decide whether to Insert/Append or Update a record.