I had a similar macro that I needed to modify. I don't know how to input the
input numbers or how you want the results. Run this code and I will modify
as required
Public InStrings
Public combo
Public RowCount
Public ComboLen
Public NumbersMatch
Public Checktotal
Sub combinations()
Checktotal = 25
NumbersMatch = 0
InStrings = Array(10, 15, 20, 25)
Length = UBound(InStrings) + 1
Level = 1
RowCount = 1
For ComboLen = 1 To Length
ReDim combo(ComboLen)
Position = 0
Call recursive(Level, Position)
Next ComboLen
End Sub
Sub recursive(ByVal Level As Integer, ByVal Position As Integer)
Length = UBound(InStrings) + 1
For i = Position To (Length - 1)
'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'124
'125
'234
'235
'245
'345
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j
If found = False Then
combo(Level - 1) = i
If Level = ComboLen Then
For j = 0 To (ComboLen - 1)
If j = 0 Then
ComboString = InStrings(combo(j))
Mytotal = Val(InStrings(combo(j)))
Else
ComboString = ComboString & "," & InStrings(combo(j))
Mytotal = Mytotal + Val(InStrings(combo(j)))
End If
Next j
Sheets("Sheet1").Range("A" & RowCount) = ComboString
Sheets("Sheet1").Range("B" & RowCount) = Mytotal
If Checktotal = Mytotal Then
NumbersMatch = NumbersMatch + 1
Sheets("Sheet1").Range("C" & RowCount) = NumbersMatch
End If
RowCount = RowCount + 1
Else
Call recursive(Level + 1, i)
End If
End If
Next i
End Sub
"Eán" wrote:
> 50 lines
>
> "Joel" wrote:
>
> > Requires a macro. How big is your table?
> >
> > "Eán" wrote:
> >
> > > Is there a function / formula I can use to determine how many combinations a
> > > string of data can return the same value?
> > > For example:
> > > 10
> > > 15
> > > 20
> > > 5
> > > There are 2 ways the above data can return the value 25.
> > >
> > > Many thanks
|
Similar Threads
