Option Explicit
Sub Sfind()
Dim rng As Range, srng As Range
Dim dic As Variant, key As Variant
Dim i As Integer, k As Integer
With Sheets("Sheet1") '这里选择表格
Set srng = .[a1:a12] '这里选择要统计重复的区域
Set dic = CreateObject("Scripting.Dictionary")
For Each rng In srng
If Not IsEmpty(rng.Value) Then
If Not dic.Exists(rng.Value) Then dic.Add rng.Value, 1
End If
Next rng
key = dic.keys
For i = LBound(key) To UBound(key)
k = 0
For Each rng In srng
If rng.Value = key(i) Then
k = k + 1
If k > 1 Then rng.Value = rng.Value & k & "号"
End If
Next rng
Next i
Set srng = Nothing
Set dic = Nothing
End With
End Sub
防止有什么我没有调试出来的BUG,运行前先备份数据!
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/5ab5c9ea15ce36d386c4484032f33a87e850b1fa?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/500fd9f9d72a60596c162b6a2034349b033bbab8?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/314e251f95cad1c8160dc956773e6709c93d5108?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)