Page History

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