Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Value of Number
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Value of Number
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 In article , Gigi wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Value of Number
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 "Gigi" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Number | Excel Discussion (Misc queries) | |||
return cardinal number from ordinal number function - an example | Excel Programming | |||
VBA: how do I return a row number? | Excel Programming | |||
VBA: how do I return a row number? | Excel Programming | |||
command to return the row number or cell number in excel? | Excel Programming |