Handle a value from a Range instead of value of a Cell

[ytayta555]

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 .

 

[Greg Glynn]

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

 

[ytayta555]

Thanks for suggestions , Greg .I'll try to apply , but I
don't know where from to begin .

 

[Gary Keramidas]

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.

 

[ytayta555]

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 .

 

[ytayta555]

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 .

 

[ytayta555]

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 .

 

[Don Guillett]

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.

 

[ytayta555]

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 .