|
- Sub 生成()
- With Sheets("data")
- Arr1 = .Range("A2:L7")
- End With
- Set D = CreateObject("Scripting.Dictionary")
- With Sheets("分析")
- ROW1 = .Range("B65536").End(xlUp).Row
- ARR2 = .Range("C2:I" & ROW1)
- ReDim ARR11(1 To UBound(ARR2), 1 To UBound(ARR2, 2))
- ReDim ARR12(1 To UBound(ARR2), 1 To UBound(ARR2, 2))
- ReDim arr13(1 To UBound(ARR2), 1 To UBound(ARR2, 2))
- For I = 1 To UBound(ARR2)
- M = 0
- For J = 1 To UBound(ARR2, 2)
- y = False
- For p = 2 To UBound(Arr1)
- For q = 1 To UBound(Arr1, 2)
- If ARR2(I, J) = Arr1(p, q) Then
- y = True
- ARR11(I, J) = Arr1(1, q)
- Exit For
- End If
- Next q
- If y = True Then Exit For
- Next p
- If Not D.Exists(ARR11(I, J)) Then
- M = M + 1
- D(ARR11(I, J)) = M
- ARR12(I, M) = ARR11(I, J)
- arr13(I, M) = Asc(ARR12(I, M)) - 64
- End If
- Next J
- D.RemoveAll
- Next I
- With .Range("K2").Resize(UBound(ARR11), UBound(ARR11, 2))
- .ClearContents
- .Value = ARR11
- End With
- With .Range("S2").Resize(UBound(ARR12), UBound(ARR12, 2))
- .ClearContents
- .Value = ARR12
- End With
- With .Range("AA2").Resize(UBound(arr13), UBound(arr13, 2))
- .ClearContents
- .Value = arr13
- End With
- End With
- End Sub
- Sub 清空()
- With Sheets("分析")
- .Range("K2:Q65536").ClearContents
- .Range("S2:Y65536").ClearContents
- .Range("AA2:AG65536").ClearContents
- End With
- End Sub
复制代码
附件1-2-1.rar
(48.71 KB, 下载次数: 2)
|
|