|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
留言看到,请参考:
- Sub Macro1()
- Dim arr, brr$(), i&, m&, s$, t$, t1$, t2$, t3$, d As Object
- Set d = CreateObject("scripting.dictionary")
- arr = Range("a2:a" & Range("a65536").End(xlUp).Row)
- On Error Resume Next
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = ""
- t = pinyin(arr(i, 1), "", 6)
- If Err.Number = 0 Then
- d(t) = ""
- Else
- Err.Clear
- End If
- Next
- arr = Sheets("数据库").Range("a1:a" & Sheets("数据库").Range("a65536").End(xlUp).Row)
- ReDim brr(1 To UBound(arr), 1 To 1)
- For i = 1 To UBound(arr)
- s = arr(i, 1)
- If d.Exists(s) Then
- m = m + 1
- brr(m, 1) = s
- Else
- t = pinyin(s, "", 6) 'pinyin是自定义函数,在模块2
- If Err.Number = 0 Then
- If d.Exists(t) Then
- m = m + 1
- brr(m, 1) = s
- Else
- If Len(s) = 3 Then
- t1 = Left$(s, 1) & Right$(s, 1)
- t2 = Left$(s, 1) & Mid$(s, 2, 1)
- t3 = Mid$(s, 2, 1) & Right$(s, 1)
- If d.Exists(t1) Or d.Exists(t2) Or d.Exists(t3) Or d.Exists(pinyin(t1, "", 6)) Or d.Exists(pinyin(t2, "", 6)) Or d.Exists(pinyin(t3, "", 6)) Then
- m = m + 1
- brr(m, 1) = s
- End If
- End If
- End If
- Else
- Err.Clear
- End If
- End If
- Next
- [b2:b65536] = ""
- [b2].Resize(m) = brr
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|