添加动态条件:
- Option Explicit
- Dim sj1, sj2, sj3, jg(), k&, L&, m&, n&, col
- Sub Combin_kagawa2()
- Dim sj, i, kk, j
- With Sheets("排列组合")
- sj = .Range("a1").CurrentRegion.Offset(1)
- col = .[q2]
- sj3 = .Range(.[o4], .Cells(4, 14 + col))
- ReDim sj2(1 To 1000, 1 To UBound(sj))
- L = 0: i = 0
- Do
- i = i + 1: n = sj(i, 1)
- If n Then
- L = L + 1: k = 0
- Do
- If sj(i, 2) <> "" Then
- ReDim sj1(1 To UBound(sj, 2))
- For j = 1 To UBound(sj, 2)
- If sj(i, j + 1) = "" Then Exit For Else sj1(j) = sj(i, j + 1)
- Next
- m = j - 1
- Call dgZH("", 0, 1)
- End If
- If sj(i + 1, 1) <> "" Then Exit Do Else i = i + 1
- Loop Until i = UBound(sj)
- If k > kk Then kk = k
- End If
- Loop Until i = UBound(sj)
- m = kk: n = L: k = 0
- .[a2].Offset(UBound(sj) + 7).CurrentRegion = ""
- .[a2].Offset(UBound(sj) + 7).Resize(m, n) = sj2
- ReDim jg(60000, 0)
- Call dgMN("", 1)
- If k = 0 Then MsgBox "无此排列组合,请重新设置条件!": Exit Sub
- .[w2].CurrentRegion = ""
- .[w2] = "共有" & k & "种组合"
- .[w3].Resize(k) = jg
- End With
- End Sub
- Sub dgZH(r$, i%, t%)
- Dim j%, jj, sjgw
- For j = i + 1 To m - n + t
- If t < n Then
- sjgw = Val(Right(Mid(r & "," & sj1(j), 2), 1))
- For jj = 1 To col
- If sjgw = sj3(1, jj) Then
- Call dgZH(r & "," & sj1(j), j, t + 1)
- End If
- Next jj
- Else
- sjgw = Val(Right(Mid(r & "," & sj1(j), 2), 1))
- For jj = 1 To col
- If sjgw = sj3(1, jj) Then
- k = k + 1
- sj2(k, L) = Mid(r & "," & sj1(j), 2)
- End If
- Next jj
- End If
- Next
- End Sub
- Sub dgMN(r$, j%)
- Dim i%, jj, sjgw, sjgw1, sjgw2
- For i = 1 To m
- If sj2(i, j) <> "" Then
- If j < n Then
- Call dgMN(r & "," & sj2(i, j), j + 1)
- Else
- sjgw = Mid(r, 2) & "," & sj2(i, j)
- sjgw1 = Split(sjgw, ",")
- For jj = 0 To UBound(sjgw1)
- sjgw2 = sjgw2 & "," & Right(sjgw1(jj), 1)
- Next jj
- sjgw2 = Mid(sjgw2, 2)
- If col = 2 Then
- If InStr(sjgw2, sj3(1, 1)) > 0 And InStr(sjgw2, sj3(1, 2)) > 0 Then
- jg(k, 0) = sjgw
- k = k + 1
- End If
- ElseIf col = 3 Then
- If InStr(sjgw2, sj3(1, 1)) > 0 And InStr(sjgw2, sj3(1, 2)) > 0 Then
- If InStr(sjgw2, sj3(1, 3)) > 0 Then
- jg(k, 0) = sjgw
- k = k + 1
- End If
- End If
- ElseIf col = 4 Then
- If InStr(sjgw2, sj3(1, 1)) > 0 And InStr(sjgw2, sj3(1, 2)) > 0 Then
- If InStr(sjgw2, sj3(1, 3)) > 0 And InStr(sjgw2, sj3(1, 4)) > 0 Then
- jg(k, 0) = sjgw
- k = k + 1
- End If
- End If
- ElseIf col = 5 Then
- If InStr(sjgw2, sj3(1, 1)) > 0 And InStr(sjgw2, sj3(1, 2)) > 0 Then
- If InStr(sjgw2, sj3(1, 3)) > 0 And InStr(sjgw2, sj3(1, 4)) > 0 Then
- If InStr(sjgw2, sj3(1, 5)) > 0 Then
- jg(k, 0) = sjgw
- k = k + 1
- End If
- End If
- End If
- End If
- sjgw2 = ""
- End If
- End If
- Next
- <span style="line-height: 1.5;">End Sub</span>
复制代码 |