question

AbdulrahmanAleisa-2879 avatar image
0 Votes"
AbdulrahmanAleisa-2879 asked GabrielDzsurdzsa-8881 answered

duplicate values in VBA range

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.

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GabrielDzsurdzsa-8881 avatar image
0 Votes"
GabrielDzsurdzsa-8881 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.