Return Value of Number

[Guest]

Hi all,
I want to have the number 44 look at row 2, and choose the largest number,
to the smallest to equal 66--and then retun that value in row 3.
Any help would be appreicated!!!!!!!!!!!!!!!!!!

44

32 24 16 12 8 4
1 1 1

GG

 

[JE McGimpsey]

If I understand you correctly (and I'm not sure I do, since I don't know
what "have the number 44 look at row 2" means, much less how to figure
out how 44 should equal 66), one way:

Assume 44 in A1, and {32,24,27,12,8,4} are in A4:F4

A5: =INT(A1/A4)
B5: =INT(($A$1-SUMPRODUCT($A4:A4,$A5:A5))/B4)

Drag B5 across to F5

 

[Guest]

I tested your data with the code below and it works. the code expects 44 in
cell A1. Also I got different results because 32 + 12 = 44.


Public InStrings
Public combo
Public ResultLength
Public ComboTotal
Public FoundResults
Sub total()

LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
ReDim InStrings(LastColumn)
For ColumnCount = 1 To LastColumn
InStrings(ColumnCount - 1) = Cells(2, ColumnCount)
Next ColumnCount

ComboTotal = Range("A1")
FoundResults = False
ResultLength = 0
Level = 1
ReDim combo(UBound(InStrings))
Position = 0

Call recursive(Level, Position)
Set SearchRange = Range("A2", Cells(2, LastColumn))
If FoundResults = True Then
For i = 1 To ResultLength
Set c = SearchRange.Find(what:=InStrings(combo(i - 1)),
LookIn:=xlValues)
c.Offset(1, 0) = 1
Next i
End If

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
temptotal = 0
For j = 0 To (Level - 1)
temptotal = temptotal + InStrings(combo(j))
Next j
If temptotal = ComboTotal Then
ResultLength = Level
FoundResults = True
Exit For
End If
If Level < Length Then
Call recursive(Level + 1, i)
End If
If FoundResults = True Then
Exit For
End If
End If
Next i
End Sub