Option Explicit
Sub test()
Dim vResult(), ar(), br(), r&, n&, strJoin$, i&, j&
With Range("A2", Cells(Rows.Count, "A").End(xlUp))
ar = .Value
n = UBound(ar)
ReDim br(1 To n)
MakeNumCount2 vResult, ar, br, r, 10
ReDim ar(1 To UBound(vResult), 0)
For i = 1 To UBound(vResult)
strJoin = ""
For j = 1 To UBound(vResult(i))
vResult(i)(j) = "[" & .Cells(vResult(i)(j), 1).Address(0, 0) & "]"
Next j
ar(i, 0) = Join(vResult(i), "+") & "=10"
Next i
End With
[D2].Resize(UBound(ar)) = ar
End Sub
Function MakeNumCount2(ByRef vResult(), ByVal ar, ByVal br, ByRef iGroup&, ByVal iSum&, _
Optional ByVal iCol& = 1, Optional ByVal iTemp& = 0, Optional ByVal iStart& = 1)
Dim i&, j&, iNum&, cr(), r&
For i = iStart To UBound(br)
iNum = ar(i, iCol): br(i) = iNum
If iTemp + iNum = iSum Then
r = 0: Erase cr
For j = 1 To UBound(br)
If br(j) <> "" Then
r = r + 1
ReDim Preserve cr(1 To r)
cr(r) = j
End If
Next j
iGroup = iGroup + 1
ReDim Preserve vResult(1 To iGroup)
vResult(iGroup) = cr
ElseIf iTemp + iNum < iSum Then
Call MakeNumCount2(vResult, ar, br, iGroup, iSum, iCol, iTemp + iNum, i + 1)
End If
br(i) = ""
Next
End Function
|