Private Done As Boolean Private CL0 As Integer Private CL1 As Integer Private CL2 As Integer Private CL3 As Integer Private CL4 As Integer Private CL5 As Integer Private CL6 As Integer Private CL7 As Integer Sub CalcCombination() Dim value As Double value = 361.1 Dim ws As Worksheet, tv As Double, tv2 As Double Set ws = Worksheets("Sheet2") Done = False For i = 1 To 32 tv = ws.Cells(i, 1) Debug.Print "Starting for Row " & i & " TestValue: " & tv & vbCrLf CL0 = i Call rec(i + 1, 32, value, tv, ws, 0) If Done Then GoTo Gedaan End If ' For j = i + 1 To 32 ' tv2 = tv + ws.Cells(j, 1) ' Next j Next i Gedaan: End Sub Sub rec(startRow As Integer, endRow As Integer, checkValue As Double, CurrentValue As Double, ws As Worksheet, depth As Integer) Dim CCV As Double '1 create for loop For i = startRow To endRow Debug.Print "Current Row: " & i & " , Depth: " & depth Select Case depth Case 0 CL1 = i Case 1 CL2 = i Case 2 CL3 = i Case 3 CL4 = i Case 4 CL5 = i Case 5 CL6 = i Case 6 CL7 = i End Select 'add and check value CCV = CurrentValue + CDbl(ws.Cells(i, 1).value) If CCV = checkValue Then Debug.Print "HURRAY!! Depth: " & depth & "Current Row: " & i MsgBox "HURRAY!! Depth: " & depth & "Current Row: " & i MsgBox " CL0: " & CL0 & " CL1: " & CL1 & " CL2: " & CL2 & " CL3: " & CL3 & " CL4: " & CL4 & " CL5: " & CL5 & " CL6: " & CL6 & " CL7: " & CL7 Debug.Print " CL0: " & CL0 & " CL1: " & CL1 & " CL2: " & CL2 & " CL3: " & CL3 & " CL4: " & CL4 & " CL5: " & CL5 & " CL6: " & CL6 & " CL7: " & CL7 Done = True End If If CCV > checkValue Then 'Debug.Print "too high, next!!" & "StartRow: " & startRow & " EndRow: " & endRow & " Current i: " & i & vbCrLf GoTo NextItem End If 'recreate range (1 less) and 'call yourself if range is more than xxx If startRow + 1 <= endRow And Not Done Then Call rec(i + 1, endRow, checkValue, CCV, ws, depth + 1) End If NextItem: Next End Sub
Add Comment