duplicate values in VBA range

Abdulrahman Aleisa 1 Reputation point

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.

0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Gabriel Dzsurdzsa 6 Reputation points

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


    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
    0 comments No comments