|
藍色部份,新增、修改
P 列格式改成 「文字」
Sub demo()
a = Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
bh = Range("b1:h" & Cells(Rows.Count, "b").End(xlUp).Row)
ReDim r(1 To UBound(bh))
ReDim n2r(1 To 99, 1 To UBound(bh))
ReDim c(1 To 99)
For i = 1 To UBound(bh)
r(i) = 2
For j = 1 To 7
n2r(bh(i, j), i) = 1
c(bh(i, j)) = c(bh(i, j)) + 1
Next
Next
o = 1
Do While 1
Max = WorksheetFunction.Max(c)
If Max <= 1 Then Exit Do
For i = 1 To 99
If c(i) = Max Then
Min = 0
For j = 1 To UBound(bh)
If n2r(i, j) Then r(j) = 1: Min = IIf(Min = 0, a(j, 1), Min)
Next
o = o + 1
Cells(o, "o").Value = Min
Cells(o, "p").Value = Format(i, "00")
End If
Next
For i = 1 To UBound(bh)
If r(i) = 1 Then
For j = 1 To 7
n2r(bh(i, j), i) = 0
c(bh(i, j)) = c(bh(i, j)) - 1
Next
r(i) = 0
End If
Next
Loop
If Max = 1 Then
For i = 1 To UBound(bh)
If r(i) Then
o = o + 1
Cells(o, "o").Value = a(i, 1)
Cells(o, "p").Resize(1, 7) = Application.Index(bh, i, 0)
End If
Next
End If
End Sub
祝順心,南無阿彌陀佛!
|
评分
-
查看全部评分
|