vendredi 29 juillet 2016

VBA - IF loop improvements.

I'm currently running a macro which identifies duplicates in a workbook, however it identifies the first set off the index and doesn't tag the first set then which has led to me setting up a if statement to by pass this, which adds duplicate to the first instance too. This is taking a long time to do however and would like to improve this, if possible. Any suggestions would be greatly appreciated, I am new to VBA but have been learning bits as I've encountered new problems!

'Declaring the lastRow variable as Long to store the last row value in the Column1
Dim lastRow As Long
'matchFoundIndex is to store the match index values of the given value
Dim matchFoundIndex As Long
'iCntr is to loop through all the records in the column 1 using For loop
    Dim iCntr As Long
    Dim first_dup As Long
    Dim tagging As Long
    Dim item_code As String

'Finding the last row in the Column 1
    lastRow = Range("B1000000").End(xlUp).Row
'
'looping through the column1
    For iCntr = 2 To lastRow

'checking if the cell is having any item, skipping if it is blank.
    If Cells(iCntr, 1) <> "" Then
'getting match index number for the value of the cell

    matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)

'if the match index is not equals to current row number, then it is a duplicate value
    If iCntr <> matchFoundIndex Then

'Printing the label in the column B
    Cells(iCntr, 4) = "Duplicate"

End If
End If
Next


            For first_dup = 2 To lastRow
            If Cells(first_dup, 5) = "Duplicate" Then
            item_code = Cells(first_dup, 1)



                 For tagging = 2 To lastRow
                If Cells(tagging, 1) = item_code Then
                Cells(tagging, 5) = "Duplicate"
                End If

      Next




            End If


                Next

Example data:
item code   
1   
2   
3   
4   
1   duplicate
2   duplicate
3   duplicate
4   duplicate
1   duplicate
2   duplicate
3   duplicate
4   duplicate

Aucun commentaire:

Enregistrer un commentaire