Excel VBA如果在另一个特定列中发现重复，则在特定列中添加重复的代码

Question

我必须在特定列中找到重复项，如果发现重复项，则放置"Duplicate"对于另一列中的重复记录。例如，我查找重复的列是"F"并且必须在"CG"栏中添加数字。你能帮帮我吗?

Sub sbFindDuplicatesInColumn_C()
Dim ws As Worksheet
'Set reference to the sheet in the workbook.
Set ws = ThisWorkbook.Worksheets("Master")
ws.Activate 'not required but allows user to view sheet if warning message appears

'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
'Finding the last row in the Column 1
lastRow = Range("F99999").End(xlUp).Row
'looping through the column1
For iCntr = 1 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("F1:F" & 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, 80) = "Duplicate"
End If
End If
Next
End Sub

Answer 1

Sub sbFindDuplicatesInColumn_C()

Dim ws As Worksheet
'Set reference to the sheet in the workbook.
Set ws = ThisWorkbook.Worksheets("Master")
ws.Activate 'not required but allows user to view sheet if warning message appears


'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
'Finding the last row in the Column 1
lastRow = Range("F99999").End(xlUp).Row
'looping through the column1
For iCntr = 1 To lastRow
'checking if the cell is having any item, skipping if it is blank.
If Cells(iCntr, 6) <> "" Then
'getting match index number for the value of the cell
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 6), Range("F1:F" & 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 CF
Cells(iCntr, 85) = "Duplicate"
End If
End If
Next
End Sub