Ian,

While you're waiting for Tom, does this help? A couple of variables (cnt,
num and tot) weren't dimensioned in the original code, which would cause
problems if you've specified "Option Explicit." I dimmed them as Longs, and
it works for me.

hth,

Doug

Sub bldbin(num As Long, bits As Long, arr() As Long)
Dim lNum, cnt, i 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 num As Long
Dim tot As Long
Dim rng As Range
Dim icol 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
rng.Offset(0, icol) = varr1
If icol = 256 Then
MsgBox "too many columns, i is " & i & " of " & num & _
" combinations checked"
Exit Sub
End If
End If
Next
End Sub
"ian123" wrote in message
...
Tom,

Thanks for your help - unfortunately its not working for me. I think
its just a small problem but i'm not experienced enough to solve it -
can you help please.

on running the macro the word ''bldbin'' in the line:
bldbin i, bits, varr1
is highlighted with the message box "compile error: sub or function not
defined"

Please excuse any elementary errors on my behalf. Once again your help
is much appreciated


---
Message posted from http://www.ExcelForum.com/