Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
A good day to you all
I have the next macro : Sub Scenarios () ' This macro return the combination of 30 things taken 5 at a time ' The 30 individual piece values are assumed to be in cells A1:A30 ' AAA will place the various combinations in column C ' Warning : the values of A1:A30 , is better to be not only numbers , ' because can do wrong results cause of cell format ; instead of value 1 is better to put ;1 ' in A1 , ;2 in A2 and so on to A30 Dim i1, i2, i3, i4, i5, i6, i7, i8, i9, i10, i11, i12, i13, i14, i15 As Long Dim i16, i17, i18, i19, i20, i21, i22, i23, i24, i25, i26, i27, i28, i29, i30 As Long Dim iRow As Long Dim iCol As Integer iRow = 0 iCol = 3 For i1 = 1 To 26 For i2 = i1 + 1 To 27 For i3 = i2 + 1 To 28 For i4 = i3 + 1 To 29 For i5 = i4 + 1 To 30 iRow = iRow + 1 Cells(iRow, iCol) = Cells(i1, "A") + Cells(i2, "A") _ + Cells(i3, "A") + Cells(i4, "A") _ + Cells(i5, "A") Next i5 Next i4 Next i3 Next i2 Next i1 End Sub _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ This macro handle the values of A1:A30 ; what I need is the next : 1} I need to handle values of Range("D1:F1") instead of cell A1 , ("D2:F2") instead of cell A2 , ("D3:F3") instead of cell A3 ..., and so on To Range("D30:F30") instead of cell A30 ; 2} I have need to generate the result in another way , not in a single cell to be all 5 results ! Value of Range("D1:F1") I need to be in Range("H1:J1") , instead of only one cell (the value of cell D1 to be in H1 , E1 in I1 , F1 in J1) ; Next Row (in Range("H2:J2")) to be the value of Range("D2:F2") , and so on , in Range("H5:J5") is ending first combination , every 5 rows is a combination of values of Range("D1:F30") Then , I need to display the results in 2 ways : 1} to generate the results in all range (Columns H:J ) ,Row 6 is beginning of the second combination ...etc. ( like the code above , to display the results in entire column , from row1 to row 65536 ) , every 5 rows being an complete combination ; 2} The second way I need to be static , the code displaing the results only in Range("H1:J5") Please to help , and thank you very much |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Please very much to help me here , I really need this
macro to work properly ; [ I use excel 2003 , xp ] . Thank you very much for your time . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |