|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 一把小刀闯天下 于 2019-12-18 11:21 编辑
'你统计表中的名字都有缺失,那就直接生成吧,,,
Option Explicit
Sub test()
Dim arr, brr, i, j, k, kk, dic, t, tt, m
Set dic = CreateObject("scripting.dictionary")
i = [a1].CurrentRegion.Rows.Count
arr = Range("a1:d" & i)
For i = 2 To UBound(arr, 1)
t = arr(i, 1): arr(i, 1) = arr(i, 2)
arr(i, 2) = t: arr(i, 3) = arr(i, 4)
Next
ReDim brr(1 To UBound(arr, 1) * 10, 3)
For i = 2 To UBound(arr, 1)
For j = 1 To 3
If Len(Trim(arr(i, j))) Then
t = Split(Trim(arr(i, j)), Space(1))
For k = 0 To UBound(t)
If Len(t(k)) Then
tt = Split(t(k), Chr(10))
For kk = 0 To UBound(tt)
If Len(tt(kk)) > 0 Then
If Not dic.exists(tt(kk)) Then
m = m + 1: dic(tt(kk)) = m
brr(m, 0) = tt(kk)
End If
brr(dic(tt(kk)), j) = brr(dic(tt(kk)), j) + 1
End If
Next
End If
Next
End If
Next
Next
With [h2]
With .Resize(UBound(brr, 1), UBound(brr, 2) + 1)
.Clear
.Value = brr
End With
If m > 0 Then .Resize(m, UBound(brr, 2) + 1).Borders.LineStyle = xlContinuous
End With
End Sub
|
评分
-
3
查看全部评分
-
|