Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
what cells add to a value
That was the wrong version of the code. Here is the later version:
Option Explicit Sub bldbin(num As Long, bits As Long, arr() As Long) Dim lNum As Long, i As Long, cnt As Long lNum = num ' Dim sStr As String ' sStr = "" cnt = 0 For i = bits - 1 To 0 Step -1 If lNum And 2 ^ i Then cnt = cnt + 1 arr(i, 0) = 1 ' sStr = sStr & "1" Else arr(i, 0) = 0 ' sStr = sStr & "0" End If Next ' If cnt = 2 Then ' Debug.Print num, sStr ' End If End Sub Sub TestBldbin() Dim i As Long Dim bits As Long Dim varr As Variant Dim varr1() As Long Dim rng As Range Dim icol As Long Dim tot As Long Dim num As Long icol = 0 Set rng = Range(Range("B1"), Range("B1").End(xlDown)) num = 2 ^ rng.Count - 1 bits = rng.Count varr = rng.Value ReDim varr1(0 To bits - 1, 0 To 0) For i = 0 To num bldbin i, bits, varr1 tot = Application.SumProduct(varr, varr1) If tot = Range("A1") Then icol = icol + 1 If icol = 255 Then MsgBox "too many columns, i is " & i & " of " & num & _ " combinations checked" Exit Sub End If rng.Offset(0, icol) = varr1 End If Next End Sub -- Regards, Tom Ogilvy "JS" wrote in message ... I have a number - say 1525 - and I have range of values - say - 135 10000 500 550 1000 25 Is there a way to enter the 1525 and have excel hightlight the figures that sum to 1525? so excel would let me know that 500, 25, 1000 add up to the 1525? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Compare 1 cell to column of cells returning adjacent cells info? | Excel Worksheet Functions | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions |