各位大侠:
不能用COUNTIF进行判断,因为这函数只能判断前十位的字符。你将一个身份证号重复,将第十一位以后的字符改掉它仍会判断为重复的,造成判断失误。
我上传一个附件。请打开文件,启用宏,设C列C2开始是身份证号列,判断结果放D2开始列,请将身份证号粘贴入C列,按判断按钮即可作出判断。
Private Sub CommandButton1_Click()
Dim row_last As Long, flag As Boolean, temp1 As Boolean, temp2 As Boolean
Dim i As Long, j As Long
' 找出最后一行
Selection.SpecialCells(xlCellTypeLastCell).Select
flag = False
Do While flag = False
If ActiveCell.Row = 1 Then
Exit Do
End If
Selection.End(xlToLeft).Select
temp1 = IsEmpty(ActiveCell.Value)
Selection.End(xlToRight).Select
temp2 = IsEmpty(ActiveCell.Value)
If temp1 = True And temp2 = True Then
Selection.Offset(-1, 0).Select
Else
flag = True
Exit Do
End If
Loop
Selection.End(xlToLeft).Select
row_last = ActiveCell.Row
'充空
For i = 2 To row_last
Cells(i, 4) = ""
Next
' 对比
For i = 2 To row_last - 1
For j = i + 1 To row_last
If Cells(i, 3) = Cells(j, 3) Then
Cells(i, 4) = "有重复"
Cells(j, 4) = "有重复"
End If
Next
Next
End Sub