Con le macro si fa "tutto" o quasi.
ma, secondo me, non ti sei posto la meta finale e stai modificando il percorso man mano che otteniamo dei risultati
questo comporta notevole perdita di tempo per entrambi
del tuo tempo ne fai quello che vuoi ma del mio ci tengo molto
ora ti dimostro che la tua ricerca della "piramide" poteva essere molto più semplice e con un'elaborazione ridotta
questa è la macro che hai richiesto (per riga)
- Codice: Seleziona tutto
Sub SommaPerRiga()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Set WS1 = Sheets("Foglio1")
Set Ws2 = Sheets("Foglio2")
Ws2.Cells.ClearContents
Dim VC(90) As Integer
Dim VR(88) As Integer
For CC = 1 To 90
VC(CC) = CC
Next CC
For RR = 1 To 88
VR(RR) = RR
Next RR
For RR1 = 1 To 88
For CC1 = RR1 To 91 - RR1 - WS1.[A1]
If VC(CC1) > WS1.[B1] - WS1.[A1] Then GoTo SaltaCC1
For CC2 = CC1 + 1 To 91 - RR1 - (WS1.[A1] + 1)
If VC(CC1) + VC(CC2) > WS1.[B1] - WS1.[A1] Then GoTo SaltaCC2
For CC3 = CC2 + 1 To 91 - RR1 - (WS1.[A1] + 2)
If VC(CC1) + VC(CC2) + VC(CC3) > WS1.[B1] Then GoTo SaltaCC3
If VC(CC1) + VC(CC2) + VC(CC3) = WS1.[B1] And WS1.[A1] = 3 Then
UR = Ws2.Cells(Rows.Count, 1 + (RR1 - 1) * (WS1.[A1] + 1)).End(xlUp).Row + 1
UC = Ws2.Cells(UR, Columns.Count).End(xlToLeft).Column
If UC >= WS1.[A1] Then UC = UC + 2
Ws2.Cells(UR, UC).Value = VC(CC1)
Ws2.Cells(UR, UC + 1).Value = VC(CC2)
Ws2.Cells(UR, UC + 2).Value = VC(CC3)
End If
If WS1.[A1] = 3 Then GoTo Salta3
For CC4 = CC3 + 1 To 91 - RR1 - (WS1.[A1] + 3)
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) > WS1.[B1] Then GoTo Salta3
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) = WS1.[B1] And WS1.[A1] = 4 Then
UR = Ws2.Cells(Rows.Count, 1 + (RR1 - 1) * (WS1.[A1] + 1)).End(xlUp).Row + 1
UC = Ws2.Cells(UR, Columns.Count).End(xlToLeft).Column
If UC >= WS1.[A1] Then UC = UC + 2
Ws2.Cells(UR, UC).Value = VC(CC1)
Ws2.Cells(UR, UC + 1).Value = VC(CC2)
Ws2.Cells(UR, UC + 2).Value = VC(CC3)
Ws2.Cells(UR, UC + 3).Value = VC(CC4)
End If
If WS1.[A1] = 4 Then GoTo Salta4
For CC5 = CC4 + 1 To 91 - RR1 - (WS1.[A1] + 4)
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) + VC(CC5) > WS1.[B1] Then GoTo Salta4
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) + VC(CC5) = WS1.[B1] Then
UR = Ws2.Cells(Rows.Count, 1 + (RR1 - 1) * WS1.[A1]).End(xlUp).Row + 1
UC = Ws2.Cells(UR, Columns.Count).End(xlToLeft).Column
If UC >= WS1.[A1] Then UC = UC + 2
Ws2.Cells(UR, UC).Value = VC(CC1)
Ws2.Cells(UR, UC + 1).Value = VC(CC2)
Ws2.Cells(UR, UC + 2).Value = VC(CC3)
Ws2.Cells(UR, UC + 3).Value = VC(CC4)
Ws2.Cells(UR, UC + 4).Value = VC(CC5)
End If
Next CC5
Salta4:
Next CC4
Salta3:
Next CC3
SaltaCC3:
Next CC2
SaltaCC2:
Next CC1
SaltaCC1:
Next RR1
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Noterai che, a parte la prima colonna (con combinazioni sempre diverse) tutte le altre ripetono le combinazioni esistenti nella prima colonna,
pertanto, penso sia inutile ripetere il ciclo 88 volte quanto ne è suffciente una sola volta
con questa macro adattata ad un solo ciclo
- Codice: Seleziona tutto
Sub SommaPerRigaRid()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Set WS1 = Sheets("Foglio1")
Set Ws2 = Sheets("Foglio2")
Ws2.Cells.ClearContents
Dim VC(90) As Integer
Dim VR(88) As Integer
For CC = 1 To 90
VC(CC) = CC
Next CC
For RR = 1 To 88
VR(RR) = RR
Next RR
RR1 = 1
For CC1 = RR1 To 91 - RR1 - WS1.[A1]
If VC(CC1) > WS1.[B1] - WS1.[A1] Then GoTo SaltaCC1
For CC2 = CC1 + 1 To 91 - RR1 - (WS1.[A1] + 1)
If VC(CC1) + VC(CC2) > WS1.[B1] - WS1.[A1] Then GoTo SaltaCC2
For CC3 = CC2 + 1 To 91 - RR1 - (WS1.[A1] + 2)
If VC(CC1) + VC(CC2) + VC(CC3) > WS1.[B1] Then GoTo SaltaCC3
If VC(CC1) + VC(CC2) + VC(CC3) = WS1.[B1] And WS1.[A1] = 3 Then
UR = Ws2.Cells(Rows.Count, 1 + (RR1 - 1) * (WS1.[A1] + 1)).End(xlUp).Row + 1
UC = Ws2.Cells(UR, Columns.Count).End(xlToLeft).Column
If UC >= WS1.[A1] Then UC = UC + 2
Ws2.Cells(UR, UC).Value = VC(CC1)
Ws2.Cells(UR, UC + 1).Value = VC(CC2)
Ws2.Cells(UR, UC + 2).Value = VC(CC3)
End If
If WS1.[A1] = 3 Then GoTo Salta3
For CC4 = CC3 + 1 To 91 - RR1 - (WS1.[A1] + 3)
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) > WS1.[B1] Then GoTo Salta3
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) = WS1.[B1] And WS1.[A1] = 4 Then
UR = Ws2.Cells(Rows.Count, 1 + (RR1 - 1) * (WS1.[A1] + 1)).End(xlUp).Row + 1
UC = Ws2.Cells(UR, Columns.Count).End(xlToLeft).Column
If UC >= WS1.[A1] Then UC = UC + 2
Ws2.Cells(UR, UC).Value = VC(CC1)
Ws2.Cells(UR, UC + 1).Value = VC(CC2)
Ws2.Cells(UR, UC + 2).Value = VC(CC3)
Ws2.Cells(UR, UC + 3).Value = VC(CC4)
End If
If WS1.[A1] = 4 Then GoTo Salta4
For CC5 = CC4 + 1 To 91 - RR1 - (WS1.[A1] + 4)
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) + VC(CC5) > WS1.[B1] Then GoTo Salta4
If VC(CC1) + VC(CC2) + VC(CC3) + VC(CC4) + VC(CC5) = WS1.[B1] Then
UR = Ws2.Cells(Rows.Count, 1 + (RR1 - 1) * WS1.[A1]).End(xlUp).Row + 1
UC = Ws2.Cells(UR, Columns.Count).End(xlToLeft).Column
If UC >= WS1.[A1] Then UC = UC + 2
Ws2.Cells(UR, UC).Value = VC(CC1)
Ws2.Cells(UR, UC + 1).Value = VC(CC2)
Ws2.Cells(UR, UC + 2).Value = VC(CC3)
Ws2.Cells(UR, UC + 3).Value = VC(CC4)
Ws2.Cells(UR, UC + 4).Value = VC(CC5)
End If
Next CC5
Salta4:
Next CC4
Salta3:
Next CC3
SaltaCC3:
Next CC2
SaltaCC2:
Next CC1
SaltaCC1:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
A questo punto, avendo i numeri in ordine (min inizio combinazione e max fine combinazione) puoi estrapolare manualmente ciò che vuoi a partire ad esempio da 50 a 70, etc
Una volta che hai stabilito cosa ti serve puoi proporre il quesito ma solo quando hai le idee chiare su cosa vuoi ottenere
perché, come dicevo, non mi sembra che tu le abbia
ciao