Hi,
This is a suitable job for recursive sub.
Public Sub TheCaller()
Dim mtx() As Long, NumberOfColumn As Long, StartNum As Long, EndNum As Long, TargetValue As Long, TotalValue As Long, CurrentRow As Long
NumberOfColumn = 3
StartNum = 0
EndNum = 3
TargetValue = 3
ReDim mtx(1 To NumberOfColumn)
Cells.ClearContents
Call TheCallee(mtx, 1, NumberOfColumn, StartNum, EndNum, 0, TargetValue, 1)
End Sub
Private Sub TheCallee(ByRef mtx, ByVal idx As Long, ByRef NumberOfColumn As Long, ByRef StartNum As Long, ByRef EndNum As Long, _
ByVal TotalValue, ByRef TargetValue As Long, ByRef CurrentRow As Long)
If idx > UBound(mtx) Then Exit Sub
For i = StartNum To EndNum
mtx(idx) = i
For j = (idx + 1) To UBound(mtx)
mtx(j) = 0
Next j
Select Case (TotalValue + mtx(idx))
Case Is < TargetValue
Call TheCallee(mtx, idx + 1, NumberOfColumn, StartNum, EndNum, TotalValue + mtx(idx), TargetValue, CurrentRow)
Case TargetValue
Cells(CurrentRow, 1).Resize(, UBound(mtx)) = mtx
CurrentRow = CurrentRow + 1
Case Is > TargetValue
Exit Sub
End Select
Next i
End Sub
You adjust to your case, by setting these values :
NumberOfColumn, StartNum, EndNum , TargetValue
For example :
- For 3 bags and 3 stones, the values are :
NumberOfColumn = 3
StartNum = 0
EndNum = 3
TargetValue = 3
- For 4 bags 8 stones, the values are :
NumberOfColumn = 4
StartNum = 0
EndNum = 8
TargetValue = 8
Regards
Bookmarks