Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaing result in Entire worksheet
Best to you all
Thanks to mr. Don Guillette , I have this code , which bring data from range GU1:IV21 in range A1:BB12 , in combinatoric order : Sub ScenariosOK() Dim i1 As Long Dim i2 As Long Dim i3 As Long Dim i4 As Long Dim i5 As Long Dim i6 As Long Dim i7 As Long Dim i8 As Long Dim i9 As Long Dim i10 As Long Dim i11 As Long Dim i12 As Long Dim iRow As Long For i1 = 1 To 10 For i2 = i1 + 1 To 11 For i3 = i2 + 1 To 12 For i4 = i3 + 1 To 13 For i5 = i4 + 1 To 14 For i6 = i5 + 1 To 15 For i7 = i6 + 1 To 16 For i8 = i7 + 1 To 17 For i9 = i8 + 1 To 18 For i10 = i9 + 1 To 19 For i11 = i10 + 1 To 20 For i12 = i11 + 1 To 21 Range("A1:BB1") = Range(Cells(i1, "GU"), Cells(i1, "IV")).Value Range("A2:BB2") = Range(Cells(i2, "GU"), Cells(i2, "IV")).Value Range("A3:BB3") = Range(Cells(i3, "GU"), Cells(i3, "IV")).Value Range("A4:BB4") = Range(Cells(i4, "GU"), Cells(i4, "IV")).Value Range("A5:BB5") = Range(Cells(i5, "GU"), Cells(i5, "IV")).Value Range("A6:BB6") = Range(Cells(i6, "GU"), Cells(i6, "IV")).Value Range("A7:BB7") = Range(Cells(i7, "GU"), Cells(i7, "IV")).Value Range("A8:BB8") = Range(Cells(i8, "GU"), Cells(i8, "IV")).Value Range("A9:BB9") = Range(Cells(i9, "GU"), Cells(i9, "IV")).Value Range("A10:BB10") = Range(Cells(i10, "GU"), Cells(i10, "IV")).Value Range("A11:BB11") = Range(Cells(i11, "GU"), Cells(i11, "IV")).Value Range("A12:BB12") = Range(Cells(i12, "GU"), Cells(i12, "IV")).Value Next i12 Next i11 Next i10 Next i9 Next i8 Next i7 Next i6 Next i5 Next i4 Next i3 Next i2 Next i1 End Sub The result of this code is in a static range , A1:BB12 ; I'd need to generate the result in entire range A1:BB65536 ;in range A1:BB12 I need to be first combination , 1,2,3,.....10,11,12 (rows of GU1:IV21), in next range("A13:BB24") must to be the second combination , 1,2,3,.......10,11,13 , in next range ("A25:BB36") must to be the 3-rd combination 1,2,3,.....10,11,14 , .... and so on to 1,2,3,....10,11,21 , then , 1,2,3,.....10,12,13 etc. .. etc.. I tried next code , and it generate the result row by row , but don't return me the right combinations : Sub HardHandleVariable() Dim i1 As Long Dim i2 As Long Dim i3 As Long Dim i4 As Long Dim i5 As Long Dim i6 As Long Dim i7 As Long Dim i8 As Long Dim i9 As Long Dim i10 As Long Dim i11 As Long Dim i12 As Long Dim iRow As Long iRow = 1 iCol = 1 For i1 = 1 To 10 For i2 = i1 + 1 To 11 For i3 = i2 + 1 To 12 For i4 = i3 + 1 To 13 For i5 = i4 + 1 To 14 For i6 = i5 + 1 To 15 For i7 = i6 + 1 To 16 For i8 = i7 + 1 To 17 For i9 = i8 + 1 To 18 For i10 = i9 + 1 To 19 For i11 = i10 + 1 To 20 For i12 = i11 + 1 To 21 iRow = iRow + 1 Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i1, "GU"), Cells(i1, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i2, "GU"), Cells(i2, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i3, "GU"), Cells(i3, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i4, "GU"), Cells(i4, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i5, "GU"), Cells(i5, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i6, "GU"), Cells(i6, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i7, "GU"), Cells(i7, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i8, "GU"), Cells(i8, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i9, "GU"), Cells(i9, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i10, "GU"), Cells(i10, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i11, "GU"), Cells(i11, "IV")).Value Range(Cells(iRow, "A"), Cells(iRow, "BB")).Value = Range(Cells(i12, "GU"), Cells(i12, "IV")).Value Next i12 Next i11 Next i10 Next i9 Next i8 Next i7 Next i6 Next i5 Next i4 Next i3 Next i2 Next i1 End Sub Thanks again through this way to mr. Don Guillette , and to everybody . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaing result in Entire worksheet
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaing result in Entire worksheet
On 8 Sep, 01:01, "Don Guillett" wrote:
This is not what I suggested. Indeed , but I made ajustaments according for my needs . Any ideea here , please ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaing result in Entire worksheet
I'd like to get some suggestions here , any kind of
suggestions . Thank you very much . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaing result in Entire worksheet
I still don't understand what you want and since you didn't explain I dropped it. Instead of moving blocks of data how about just hiding rows? -- Don Guillett Microsoft MVP Excel SalesAid Software "ytayta555" wrote in message ... I'd like to get some suggestions here , any kind of suggestions . Thank you very much . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to reference entire worksheet in new worksheet | Excel Worksheet Functions | |||
Date Entry in 1 Cell with result populating for entire week in ano | Excel Discussion (Misc queries) | |||
How do I copy a check box result from Worksheet A to Worksheet B? | Excel Worksheet Functions | |||
VB for an entire worksheet | Excel Programming | |||
How to change #DIV/0! result for an entire worksheet ? | Excel Discussion (Misc queries) |