LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 247
Default 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 .
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
fill handle and cell drag-and-drop jfh14 Excel Discussion (Misc queries) 1 October 8th 08 08:24 PM
handle error in cell values Junior728 Excel Programming 1 March 4th 08 09:29 AM
How to make sub handle more than 1 cell ? Mikaela Excel Programming 4 October 16th 07 04:34 PM
Fill handle turned into a move handle Northwoods Excel Discussion (Misc queries) 1 March 2nd 07 03:40 PM
Handle to a cell (Row and Column) prakash Excel Discussion (Misc queries) 1 September 12th 06 11:35 AM


All times are GMT +1. The time now is 05:11 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"