Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
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 ' macro Scenarios 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") This is what is important , and only if it possible and next : 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") Thank you very much in advance . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
I haven't got a clue what you're asking, but can I suggest you look at
the OFFSET keyword. ie: Range("A1").OFFSET(0,1).VALUE gives the value of A1 plus one column ("B1") ie: Range("A1").OFFSET(1,1).VALUE gives the value of A1 plus one row, plus one column ("B2") Hope this helps a little. Grg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
On 2 Sep, 01:54, Greg Glynn wrote:
Thanks for suggestions , Greg .I'll try to apply , but I don't know where from to begin . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
not sure what you're asking either, but dimming the variables like this:
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 only dims i15 and i30 as long, all of the others are variants. create a breakpoint on this line, for example: For i1 = 1 To 26 then click on the view menu and then locals window execute your code and it will stop at the for line. look at the variable type in the locals window. they will all show variant except the 2 that you explicitly set to long. just some info. -- Gary "ytayta555" wrote in message ... On 2 Sep, 01:54, Greg Glynn wrote: Thanks for suggestions , Greg .I'll try to apply , but I don't know where from to begin . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
On 2 Sep, 02:48, "Gary Keramidas" <GKeramidasATmsn.com wrote:
not sure what you're asking either, but dimming the variables like this: 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 only dims i15 and i30 as long, all of the others are variants. look at the variable type in the locals window. they will all show variant except the 2 that you explicitly set to long. Thanks so much ! What I need is to work with values from a Range , like D1:F1 ; now , this macro work with values from cell A1 , A2 ... .........A30 ; Can this macro work with values from a range , or only with value from a cell ?(only with variables).. I need this code to work to combine the rows with values inside them in combinatoric order , this is the purpose . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
On 2 Sep, 02:48, "Gary Keramidas" <GKeramidasATmsn.com wrote:
not sure what you're asking either, but dimming the variables like this: 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 only dims i15 and i30 as long, all of the others are variants. In initial code there was dim Dim i1 As Long Dim i2 As Long Dim i3 As Long ...etc but , for a smaller space posting here , I wrote so ... Many thanks for fix this aspect . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
Nobody can work with this code and make changes
in it to help me to get what I need ? Please for help ! Many thanks in advance . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
Perhaps if we could understand what you want we could help. Before/after
examples. If all else fails, you may send your workbook to my address below and I'll try to take a look later in the day as I will be out of the office for awhile. -- Don Guillett Microsoft MVP Excel SalesAid Software "ytayta555" wrote in message ... Nobody can work with this code and make changes in it to help me to get what I need ? Please for help ! Many thanks in advance . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handle a value from a Range instead of value of a Cell
On 2 Sep, 18:25, "Don Guillett" wrote:
Perhaps if we could understand what you want we could help. Before/after examples. If all else fails, you may send your workbook to my address below and I'll try to take a look later in the day as I will be out of the office for awhile. Thank you so much ! Thank you so much ... I have done my homework , and I could resolve the problems : first 1} , 2} and 2} . Now , my code look so : Sub Scenarios() 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 iRow1 As Long 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 + 4 Range("H1") = Cells(i1, "D").Value Range("I1") = Cells(i1, "E").Value Range("J1") = Cells(i1, "F").Value Range("H2") = Cells(i2, "D").Value Range("I2") = Cells(i2, "E").Value Range("J2") = Cells(i2, "F").Value Range("H3") = Cells(i3, "D").Value Range("I3") = Cells(i3, "E").Value Range("J3") = Cells(i3, "F").Value Range("H4") = Cells(i4, "D").Value Range("I4") = Cells(i4, "E").Value Range("J4") = Cells(i4, "F").Value Range("H5") = Cells(i5, "D").Value Range("I5") = Cells(i5, "E").Value Range("J5") = Cells(i5, "F").Value Next i5 Next i4 Next i3 Next i2 Next i1 End Sub Now , the result of combination is static , the code displaing the results only in Range("H1:J5") , and this resolvad my second 2} problem , and first 1} and 2} . What remain unresolved is to generate the results in all range (Columns H:J ) ,Row 6 is beginning of the second combination ...( to display the results in entire columns , from row1 to row 65536 ) , every 5 rows being an complete combination (Rows 1 to 5 = 1,2,3,4,5 ; Rows 6 to 10 = 1,2,3,4,6 ; Rows 11 to 15 = 1,2,3,4,7 etc .....to Row 65536) Thanks so much for your atitude for help . Thank you so much you give me acces to your help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill handle and cell drag-and-drop | Excel Discussion (Misc queries) | |||
handle error in cell values | Excel Programming | |||
How to make sub handle more than 1 cell ? | Excel Programming | |||
Fill handle turned into a move handle | Excel Discussion (Misc queries) | |||
Handle to a cell (Row and Column) | Excel Discussion (Misc queries) |