Function GetCombination2(CoinsRange As Range, SumCellId As Range) As String Dim I& ReDim A(1 To CoinsRange.Count) ReDim Amin(1 To CoinsRange.Count) Smin = 1E+308 RR 1, CoinsRange, (SumCellId), 0 For I = 1 To CoinsRange.Count If Amin(I) Then GetCombination2 = GetCombination2 & Amin(I) & " of " & CoinsRange(I) & " " Next I End Function
Sub RR(N&, RN As Range, TR#, S#) Dim C&, SS# For C = (TR - S - 1) \ RN(N) + 1 To 0 Step -1 SS = S + C * RN(N) A(N) = C If SS >= TR And SS < Smin Then Smin = SS Amin = A End If If N < RN.Count Then RR N + 1, RN, TR, SS Next C End Sub
[/vba] работает (вроде?) с любым количеством чисел CoinsRange в пределах разумного.
Bob6320, попробуйте- [vba]
Код
Dim A#(), Amin#(), Smin#
Function GetCombination2(CoinsRange As Range, SumCellId As Range) As String Dim I& ReDim A(1 To CoinsRange.Count) ReDim Amin(1 To CoinsRange.Count) Smin = 1E+308 RR 1, CoinsRange, (SumCellId), 0 For I = 1 To CoinsRange.Count If Amin(I) Then GetCombination2 = GetCombination2 & Amin(I) & " of " & CoinsRange(I) & " " Next I End Function
Sub RR(N&, RN As Range, TR#, S#) Dim C&, SS# For C = (TR - S - 1) \ RN(N) + 1 To 0 Step -1 SS = S + C * RN(N) A(N) = C If SS >= TR And SS < Smin Then Smin = SS Amin = A End If If N < RN.Count Then RR N + 1, RN, TR, SS Next C End Sub
[/vba] работает (вроде?) с любым количеством чисел CoinsRange в пределах разумного.Апострофф
Function GetCombination3(CoinsRange As Range, SumCellId As Range) As String Dim I&, TS# Dim N& TS = SumCellId For I = 1 To CoinsRange.Count N = TS \ CoinsRange(I) TS = TS - CoinsRange(I) * N If TS > 0 Then If I = CoinsRange.Count Then N = N + 1 ElseIf TS > CoinsRange(I + 1) Then N = N + 1 TS = TS - CoinsRange(I) End If End If If N > 0 Then GetCombination3 = vbLf & GetCombination3 & N & " of " & CoinsRange(I) '& " " Next I GetCombination3 = Mid$(GetCombination3, 2) End Function
[/vba] А вот разделить результат по ячейкам с помощью UDF не знаю как.
[vba]
Код
Function GetCombination3(CoinsRange As Range, SumCellId As Range) As String Dim I&, TS# Dim N& TS = SumCellId For I = 1 To CoinsRange.Count N = TS \ CoinsRange(I) TS = TS - CoinsRange(I) * N If TS > 0 Then If I = CoinsRange.Count Then N = N + 1 ElseIf TS > CoinsRange(I + 1) Then N = N + 1 TS = TS - CoinsRange(I) End If End If If N > 0 Then GetCombination3 = vbLf & GetCombination3 & N & " of " & CoinsRange(I) '& " " Next I GetCombination3 = Mid$(GetCombination3, 2) End Function
[/vba] А вот разделить результат по ячейкам с помощью UDF не знаю как.Апострофф