|
本帖最后由 yearabc 于 2022-6-1 22:14 编辑
我想把区域内数字按一定规律归类,VBA函数设计好了,也看见在运算,怎么就没有结果呢?
Sub aa()
Set d = CreateObject("Scripting.Dictionary")
ar = [N3:S6]: ReDim br(9)
For j = 1 To UBound(ar, 2)
For i = 1 To UBound(ar)
For x = 0 To 9
If InStr(ar(i, j), x) Then d(x) = d(x) + 1
Next
Next
On Error Resume Next
For y = 0 To 9
br(y) = IIf(d(y) > br(y), d(y), br(y))
Next
d.RemoveAll
Next
For i = 11 To 14
For x = 0 To 9
If br(x) = Cells(i, 14) Then s = s & x
Next
Cells(i, 15) = s: s = ""
Next
Call zz
End Sub
Sub zz()
Set d = CreateObject("Scripting.Dictionary")
ar = [N7:S10]: ReDim br(9)
For j = 1 To UBound(ar, 2)
For i = 1 To UBound(ar)
For x = 0 To 9
If InStr(ar(i, j), x) Then d(x) = d(x) + 1
Next
Next
On Error Resume Next
For y = 0 To 9
br(y) = IIf(d(y) > br(y), d(y), br(y))
Next
d.RemoveAll
Next
For i = 11 To 14
For x = 0 To 9
If br(x) = Cells(i, 14) Then s = s & x
Next
Cells(i, 18) = s: s = ""
Next
End Sub
宏aa目的是 N3:S6中的数字在每行出现1次的归类在O11,出现2次的放在O12,出现3次的放在O13,出现4次的放在O14
宏zz目的是 N7:S10中的数字在每行出现1次的归类在R11,出现2次的放在R12,出现3次的放在R13,出现4次的放在R14
本帖最后由 hasyh2008 于 2022-6-9 09:20 编辑
Sub 统计()
Dim Arr(), Brr, Crr(1 To 4, 1 To 1), X%, Y%, K%
Dim Str$
Dim D
Set D = CreateObject("scripting.dictionary")
With Sheet1
Arr = .Range("N3:S6")
For X = 1 To UBound(Arr)
For Y = 1 To UBound(Arr, 2)
For K = 0 To 9
If InStr(Arr(X, Y), K) Then D(K) = D(K) + 1
Next K
Next Y
Next X
Arr = Application.Transpose(Array(D.keys, D.items))
For X = 1 To UBound(Arr)
Select Case Arr(X, 2)
Case Is = 1
Crr(1, 1) = Crr(1, 1) & Arr(X, 1) & ","
Case Is = 2
Crr(2, 1) = Crr(2, 1) & Arr(X, 1) & ","
Case Is = 3
Crr(3, 1) = Crr(3, 1) & Arr(X, 1) & ","
Case Is = 4
Crr(4, 1) = Crr(4, 1) & Arr(X, 1) & ","
End Select
Next X
.Range("O11").Resize(4, 1) = ""
.Range("O11").Resize(4, 1) = Crr
End With
End Sub
|
|