Looping the desired code with the changing cell range

Question

Okay, so I was thinking and came up with a better solution if it is doable. How about the macro would be something like this doing the thing in the comments?

    Sub CopyCombinationForLP()
    ' CopyCombinationForLP Macro
    ' Copy Combinations For Linear Programming Working And Copy Results in New Sheet
    ' Need this part to loop for a number of times depending on "cell value P1":

Sheets("Combinations Prior LP").Select
Range("P6:Z6").Select 'need this range to change +1 row every loop for a total of "cell value in P1" in sheet "Combination Prior LP
Selection.Copy
Sheets("Linear Programming Combination ").Select
Range("A1").Select 'Need this range stays the same for each loop
ActiveSheet.Paste Link:=True
Sheets("LP Combination 1 ").Select
Range("A2:G32").Select 'need this range to stay the same for each loop
Application.CutCopyMode = False
Selection.Copy
Sheets("Linear Programming Combination ").Select
Range("A2").Select 'Need this range to stay the same for each loop
ActiveSheet.Paste
Range("I10").Select 'This one stays the same foe every loop
Range("B32:E32").Select 'Need this range to stay the same for each loop
Application.CutCopyMode = False
Selection.Copy
Sheets("Linear Programming Results").Select
Range("A2").Select 'need this one to shift +1 row every loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Combinations Prior LP").Select

End Sub

I think this is a doable solution given that I only need the table of results from the linear programming combination. The maximum Combination I can achieve is 10000. Below are link to screenshots of what I am trying to copy and paste:

First Step : https://dl.dropboxusercontent.com/u/83126653/Combinations%20Prior%20LP.png

Second Step: https://dl.dropboxusercontent.com/u/83126653/Linear%20Programming%20Combination.png

Hope this help more. Please help me

may be insteresing: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Apr 14 '14 at 07:43
Third Step: https://dl.dropboxusercontent.com/u/83126653/LP%20Combination%201.png Fouth Step: https://dl.dropboxusercontent.com/u/83126653/Linear%20Programming%20Results.png — Nicholas Agius, Apr 14 '14 at 10:25

score 1 · Answer 1 · answered Apr 13 '14 at 23:37

Try something like:

Sub CopyCombinationForLP()
    ' CopyCombinationForLP Macro
    ' Copy Combinations For Linear Programming Working And Copy Results in New Sheet
    ' Need this part to loop for 10000 times with the ranges changing accordingly:
    Dim Ctr As Long
    For Ctr = 0 To 99999
        Sheets("Combinations Prior LP").Select
        Range("P6:Z6").Offset(Ctr, 0).Select    'need this range to change +1 row every loop for 10000 rows (next one would be P7:Z7)
        Selection.Copy
        Sheets("Linear Programming Combination ").Select
        Range("A1").Offset(0, Ctr * 12).Select  'Need this range to change +12 columns every loop (next would be cell M1)
        ActiveSheet.Paste Link:=True
        Sheets("LP Combination 1 ").Select
        Range("A2:G32").Select     'need this range to stay the same for each loop
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Linear Programming Combination ").Select
        Range("A2").Offset(0, Ctr * 12).Select     'Need this range to change +12 columns every loop (next would be cell M2)
        ActiveSheet.Paste
        Range("I10").Select     'This one stays the same foe every loop
        Range("A32:G32").Offset(0, Ctr * 12).Select     'Need this one to change +12 from first cell of range (next would be M32:S32)
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Linear Programming Results").Select
        Range("A2").Offset(Ctr, 0).Select     'need this one to shift +1 row every loop
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                      :=False, Transpose:=False
        Sheets("Combinations Prior LP").Select
    Next
End Sub

However this will not work as earlier versions of Excel have 255 columns, and even the latest versions of Excel have only 16384 columns; your 'Need this range to change +12 columns every loop (next would be cell M2)-type comments would result in an overflow in about 1366 cycles. So, either there is something you're not telling us about your data, or you got the number of loops wrong (10000 instead of 1000).

I Updated The question because of the column problem. Do you think you can help me again please? and i posted screenshots to maybe help you visualize what i am working with. Thanks in advance. — Nicholas Agius, Apr 14 '14 at 10:27

score 0 · Answer 2 · answered Apr 14 '14 at 12:00

This Was Resolved Thanks to a little help from Monty wild. the code i was looking for is this:

  Sub ResultsOfCombinations()
' CopyCombinationForLP Macro
' Copy Combinations For Linear Programming Working And Copy Results in New Sheet
' Need this part to loop for a number of times depending on "cell value P1":
       Dim Ctr As Long
       For Ctr = 0 To Sheets("Combinations Prior LP").Range("P1").Value
             Sheets("Combinations Prior LP").Select
             Range("P6:Z6").Offset(Ctr, 0).Select    'need this range to change +1 row every loop for Cell Value in P1 in sheet Combination Prior LP rows (next one would be P7:Z7)
             Selection.Copy
             Sheets("Linear Programming Combination ").Select
             Range("A1").Select
             ActiveSheet.Paste Link:=True
             Sheets("LP Combination 1 ").Select
             Range("A2:G32").Select     'need this range to stay the same for each loop
             Application.CutCopyMode = False
             Selection.Copy
             Sheets("Linear Programming Combination ").Select
             Range("A2").Select  'Need this range to stay the same for every loop
             ActiveSheet.Paste
             Range("I10").Select     'This one stays the same foe every loop
             Range("B32:E32").Select  'Need this range to stay the same for every loop
             Application.CutCopyMode = False
             Selection.Copy
             Sheets("Linear Programming Results").Select
             Range("A2").Offset(Ctr, 0).Select     'need this one to shift +1 row every loop
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                  :=False, Transpose:=False
             Sheets("Combinations Prior LP").Select
       Next
End Sub

Looping the desired code with the changing cell range

2 Answers2