Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data mix and listing
Option Explicit
Sub tester9() Dim arr(0 To 9) As Long Dim arr1(1 To 3) As Long Dim cnt As Long, varr As Variant Dim j As Long, i As Long, k As Long Dim m As Long 'Dim sStr as String varr = Range("C10:L10") arr1(1) = Range("M10").Value j = 70 Range("C70").CurrentRegion.ClearContents For i = 1 To 1023 bldArr i, arr, cnt If cnt = 2 Then m = 2 ' sStr = "" For k = 0 To 9 ' sStr = sStr & arr(k) If arr(k) = 1 Then If varr(1, k + 1) = 0 Then Exit For End If arr1(m) = varr(1, k + 1) m = m + 1 End If If m = 4 Then Exit For Next ' Debug.Print i, sStr, m If m = 4 Then Cells(j, 3).Resize(1, 3).Value = arr1 j = j + 1 End If End If Next End Sub Sub bldArr(num As Long, arr() As Long, cnt As Long) Dim lNum As Long, i As Long lNum = num Dim sStr As String sStr = "" cnt = 0 For i = 9 To 0 Step -1 If lNum And 2 ^ i Then cnt = cnt + 1 arr(9 - i) = 1 sStr = sStr & "1" Else arr(9 - i) = 0 sStr = sStr & "0" End If Next ' If cnt = 2 Then ' Debug.Print num, sStr ' End If End Sub -- Regards, Tom Ogilvy "Rick" wrote in message ... Help required please. IN cell M5, I enter a number. In cell range C10:G10 I enter other numbers all different from the number in M5. Sometimes there are empty cells in the this range (C10:L10). e.g M5 = 24 C10=27 D10=52 E10=9 F10=63 G10=empty H to L=empty Using a macro how can I list in three columns starting from C70 to E+, all the combinations of these numbers with the number in M5 as a common number in all the combinations. The (common) number entered in M5 is always in column C. C D E R70 24 27 52 71 24 27 9 72 24 27 63 73 24 52 27 74 24 52 9 75 24 52 63 76 24 9 27 77 24 9 52 and continuing until all combinations are listed. Later, as I develop my spreadsheet further, I can see a need to also have two other macro's to basically do the same thing as above, but the common number (M5) will be exclusively in column D and the macro should then find all combinations and list them in C70 to E+. The third macro will do the same but have the common number exclusively in column D and calc all the combinations. If anyone can help with the first macro, could I just alter some of the code to achieve the next two solutions. If so, could you give me some thoughts on what to alter in the macro. Thankyou in advance. Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data mix and listing
Thanks Tom,
Regards, Rick -----Original Message----- Option Explicit Sub tester9() Dim arr(0 To 9) As Long Dim arr1(1 To 3) As Long Dim cnt As Long, varr As Variant Dim j As Long, i As Long, k As Long Dim m As Long 'Dim sStr as String varr = Range("C10:L10") arr1(1) = Range("M10").Value j = 70 Range("C70").CurrentRegion.ClearContents For i = 1 To 1023 bldArr i, arr, cnt If cnt = 2 Then m = 2 ' sStr = "" For k = 0 To 9 ' sStr = sStr & arr(k) If arr(k) = 1 Then If varr(1, k + 1) = 0 Then Exit For End If arr1(m) = varr(1, k + 1) m = m + 1 End If If m = 4 Then Exit For Next ' Debug.Print i, sStr, m If m = 4 Then Cells(j, 3).Resize(1, 3).Value = arr1 j = j + 1 End If End If Next End Sub Sub bldArr(num As Long, arr() As Long, cnt As Long) Dim lNum As Long, i As Long lNum = num Dim sStr As String sStr = "" cnt = 0 For i = 9 To 0 Step -1 If lNum And 2 ^ i Then cnt = cnt + 1 arr(9 - i) = 1 sStr = sStr & "1" Else arr(9 - i) = 0 sStr = sStr & "0" End If Next ' If cnt = 2 Then ' Debug.Print num, sStr ' End If End Sub -- Regards, Tom Ogilvy "Rick" wrote in message ... Help required please. IN cell M5, I enter a number. In cell range C10:G10 I enter other numbers all different from the number in M5. Sometimes there are empty cells in the this range (C10:L10). e.g M5 = 24 C10=27 D10=52 E10=9 F10=63 G10=empty H to L=empty Using a macro how can I list in three columns starting from C70 to E+, all the combinations of these numbers with the number in M5 as a common number in all the combinations. The (common) number entered in M5 is always in column C. C D E R70 24 27 52 71 24 27 9 72 24 27 63 73 24 52 27 74 24 52 9 75 24 52 63 76 24 9 27 77 24 9 52 and continuing until all combinations are listed. Later, as I develop my spreadsheet further, I can see a need to also have two other macro's to basically do the same thing as above, but the common number (M5) will be exclusively in column D and the macro should then find all combinations and list them in C70 to E+. The third macro will do the same but have the common number exclusively in column D and calc all the combinations. If anyone can help with the first macro, could I just alter some of the code to achieve the next two solutions. If so, could you give me some thoughts on what to alter in the macro. Thankyou in advance. Regards . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data mix and listing
Hi Tom,
Could you have a look at the code. If I enter 8 in M10 and 2, 4, 5, 6, 7, 9 in C10, D10, E10, F10, G10, H10 respectively, the code runs without error, but some of the combinations are missing. For example:- 8,7,2 8,7,4 8,7,5 8,9,2 8,7,6 8,6,2 8,6,5 8,6,4 8,9,4 etc etc This is what I get 8 7 9 8 6 9 8 6 7 8 5 9 8 5 7 8 5 6 8 4 9 8 4 7 8 4 6 8 4 5 8 2 9 8 2 7 8 2 6 8 2 5 8 2 4 Also, what change do I need to do to the code to have the value in M10 appear in the second column only with all the combinations and also to have the value in M10 appear exclusively in the third column with all the other combinations. I will use 3 separate macros. Cheers Rick -----Original Message----- Option Explicit Sub tester9() Dim arr(0 To 9) As Long Dim arr1(1 To 3) As Long Dim cnt As Long, varr As Variant Dim j As Long, i As Long, k As Long Dim m As Long 'Dim sStr as String varr = Range("C10:L10") arr1(1) = Range("M10").Value j = 70 Range("C70").CurrentRegion.ClearContents For i = 1 To 1023 bldArr i, arr, cnt If cnt = 2 Then m = 2 ' sStr = "" For k = 0 To 9 ' sStr = sStr & arr(k) If arr(k) = 1 Then If varr(1, k + 1) = 0 Then Exit For End If arr1(m) = varr(1, k + 1) m = m + 1 End If If m = 4 Then Exit For Next ' Debug.Print i, sStr, m If m = 4 Then Cells(j, 3).Resize(1, 3).Value = arr1 j = j + 1 End If End If Next End Sub Sub bldArr(num As Long, arr() As Long, cnt As Long) Dim lNum As Long, i As Long lNum = num Dim sStr As String sStr = "" cnt = 0 For i = 9 To 0 Step -1 If lNum And 2 ^ i Then cnt = cnt + 1 arr(9 - i) = 1 sStr = sStr & "1" Else arr(9 - i) = 0 sStr = sStr & "0" End If Next ' If cnt = 2 Then ' Debug.Print num, sStr ' End If End Sub -- Regards, Tom Ogilvy "Rick" wrote in message ... Help required please. IN cell M5, I enter a number. In cell range C10:G10 I enter other numbers all different from the number in M5. Sometimes there are empty cells in the this range (C10:L10). e.g M5 = 24 C10=27 D10=52 E10=9 F10=63 G10=empty H to L=empty Using a macro how can I list in three columns starting from C70 to E+, all the combinations of these numbers with the number in M5 as a common number in all the combinations. The (common) number entered in M5 is always in column C. C D E R70 24 27 52 71 24 27 9 72 24 27 63 73 24 52 27 74 24 52 9 75 24 52 63 76 24 9 27 77 24 9 52 and continuing until all combinations are listed. Later, as I develop my spreadsheet further, I can see a need to also have two other macro's to basically do the same thing as above, but the common number (M5) will be exclusively in column D and the macro should then find all combinations and list them in C70 to E+. The third macro will do the same but have the common number exclusively in column D and calc all the combinations. If anyone can help with the first macro, could I just alter some of the code to achieve the next two solutions. If so, could you give me some thoughts on what to alter in the macro. Thankyou in advance. Regards . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pulling data from one sheet and listing selected data in another | Excel Worksheet Functions | |||
Listing data without blank rows | Excel Worksheet Functions | |||
listing and selecting data | Excel Worksheet Functions | |||
Listing data | Excel Discussion (Misc queries) | |||
selecting and listing data | Excel Worksheet Functions |