Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells in data range at random to determine a given number
Is there any macro or script that can be run in Excel that allows you to add
random cells to determine a fixed number? For example, a simple data range in a column could be as follows: 1,3,5,7,9 and I want to know which cells equal '8' - the script will then add cells in a sequenced order e.g. 1+3, 1+5, 1+7 ... 3+1, 3+5 .... 1+3+5, 1+3+7 etc. until it finds the right answer, or a selection of answers (e.g. in my example above, it could be 1+7 & 5+3). I'm aware that this will be very processor intensive and will probably take a long time to run, but is this possible and has a macro / script already been written that I can purchase / use? Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells in data range at random to determine a given number
This code works. these two line change to get diffferent results
Data = Array(1, 3, 6, 8) FindTotal = 12 I didn't know hwre you data originated so Isimple put the values in an array. code start below here Public Data Public RowCount Public TempArray(100) Public FindTotal Public DataLen Sub gettcombinations() Dim Level As Variant Dim CountDigits As Integer Data = Array(1, 3, 6, 8) FindTotal = 12 DataLen = UBound(Data) + 1 RowCount = 1 For CountDigits = 1 To (DataLen + 1) Level = 1 Call RecursiveAdd(Level, CountDigits) Next CountDigits End Sub Sub RecursiveAdd(Level, CountDigits) Dim count As Integer For count = 1 To DataLen 'check to see if number already in array 'temp array contains the index of tthe array 'not the actual number Found = False For ArrayIndex = 1 To (Level - 1) If TempArray(ArrayIndex) = count Then Found = True Exit For End If Next ArrayIndex If Found = False Then TempArray(Level) = count If Level = CountDigits Then Total = 0 For i = 1 To Level Total = Total + Data(TempArray(i) - 1) Next i If Total = FindTotal Then For j = 1 To Level Cells(RowCount, j) = Data(TempArray(j) - 1) Next j RowCount = RowCount + 1 End If Else Call RecursiveAdd(Level + 1, CountDigits) End If End If Next count End Sub "Valiant" wrote: Is there any macro or script that can be run in Excel that allows you to add random cells to determine a fixed number? For example, a simple data range in a column could be as follows: 1,3,5,7,9 and I want to know which cells equal '8' - the script will then add cells in a sequenced order e.g. 1+3, 1+5, 1+7 ... 3+1, 3+5 .... 1+3+5, 1+3+7 etc. until it finds the right answer, or a selection of answers (e.g. in my example above, it could be 1+7 & 5+3). I'm aware that this will be very processor intensive and will probably take a long time to run, but is this possible and has a macro / script already been written that I can purchase / use? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I determine which data in a range add up to a fixed number? | Excel Worksheet Functions | |||
Adding random #s in a range to equal a specific # | Excel Worksheet Functions | |||
Finding Number Within Range Then Copying Data Below Number to Cells | Excel Programming | |||
Adding input box number to range of cells values | Excel Programming | |||
Adding two columns to determine number of days to pay | Excel Programming |