duplicate values in VBA range

Question

Hello Everyone. I am a new student to VBA and am amazed by the unlimited capabilities of it. I honestly need ur help in my first macro. I created a macro that extracts monthly sales from a text file, formats it, and then sends it to the master sheet with sales for previous months. the Function adds new data at the bottom of the master sheet. I created a button that will trigger this entire action, but what if i accidentally click the button twice and the information is duplicated? your support is sincerely appreciated.

Answer 1

Here's a function that may help you do just that. Read the full article to help you call the function:

prevent-data-duplication-in-excel-vba

Function detect_duplicates(ByVal incomingData, dataToMatch, dataMatchRatio)

  'Loop first through new data array
  For a = 1 To UBound(incomingData)
  'Nested loop through comparison data array
    For b = a To UBound(dataToMatch)
        'Compare values between arrays
        If incomingData(a) = dataToMatch(b) Then
            'If values are the same then increase duplicateRatio
            duplicateRatio = duplicateRatio + 1

        End If

      Next b

    Next a
    'Case the actual percentage of similarity
    Select Case (duplicateRatio / UBound(dataToMatch))
        'If the percentage exceeds threshold value
        Case Is >= dataMatchRatio
            'Prompt user to identify threshold and to alert that threshold is exceeded
            MsgBox "A " & (dataMatchRatio * 100) & "% match or over has been found! You may be duplicating data. Aborting..."
            'Data set is a duplicate
            detect_duplicates = 1

        Case Is < dataMatchRatio
            'Data set is not a duplicate
            detect_duplicates = 0

    End Select

End Function