Sub test1()
Const Limite = 1000
Dim nbNombre1 As Long, nbNombre2 As Long, Lim As Long
Application.ScreenUpdating = False
AleaAsommeFixe Range("e7"), 0.25, 0.35, 180, nbNombre1
Do While Lim <= Limite
Lim = Lim + 1
AleaAsommeFixe Range("F7"), 0.25, 0.35, 180, nbNombre2
If nbNombre2 = nbNombre1 Then Exit Do
DoEvents
Loop
If Lim > Limite Then MsgBox "Pas de solution trouvée, réessayez votre chance svp.", vbCritical
Application.ScreenUpdating = True
MsgBox "Solution trouvée: " & vbLf & vbLf & nbNombre2 & " nombres " & _
vbLf & "pour une somme de " & Application.Sum(Range("e7").Resize(nbNombre2)), vbInformation
End Sub
Sub AleaAsommeFixe(Debut As Range, ByVal BorneInf, ByVal BorneSup, ByVal Somme, ByRef nbRetour As Long)
' Debut => cellule à partir de laquelle on affiche les résultats
' BorneInf => une des bornes des nombres à utiliser
' BorneSup => l'autre borne des nombres à utiliser
' Somme => la somme à trouver
' les trois derniers paramètres peuvent être des cellules
Const Limite = 500000 'au cas où
Dim x, y, coef, max&, aux, tot&, i&, decim&, N&, diff&, k&, nfois&
With Debut.Parent
.Range(.Cells(Debut.Row, Debut.Column), .Cells(.Rows.Count, Debut.Column)).Clear
End With
If BorneSup < BorneInf Then aux = BorneSup: BorneSup = BorneInf: BorneInf = aux
decim = Len(Mid(BorneInf, Len(Int(BorneInf)) + 2, 99))
x = Len(Mid(BorneSup, Len(Int(BorneSup)) + 2, 99))
If x > decim Then decim = x
coef = 10 ^ decim
BorneInf = Int(BorneInf * coef)
BorneSup = Int(BorneSup * coef)
Somme = Somme * coef
max = 1 + Int(Somme / BorneInf)
ReDim t(1 To max, 1 To 1)
Randomize
For i = 1 To UBound(t)
t(i, 1) = Int((BorneSup - BorneInf + 1) * Rnd + BorneInf)
tot = tot + t(i, 1)
If tot >= Somme Then Exit For
Next
N = i
Do While tot <> Somme
nfois = nfois + 1
If nfois > Limite Then
MsgBox "Echec de la recherche.", vbCritical
Exit Sub
End If
k = 1 + Int(Rnd * N)
If t(k, 1) > BorneInf Then t(k, 1) = t(k, 1) - 1: tot = tot - 1
Loop
For i = 1 To N: t(i, 1) = Round(t(i, 1) / coef, decim): Next
With Debut.Parent
.Cells(Debut.Row, Debut.Column).Resize(N) = t
End With
nbRetour = N
End Sub