Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

[nixon72]

Hello,

After spending too much time on this problem, I am hoping to find some help here - seems like the right place. This is the last bit of code out of a larger macro - which (except for this bit) works fine.

I have one dynamically changing column - i.e. a column within which Solver loops through 19 rows and solves a bond math problem. That works fine. The trouble comes when I then try to copy/paste the results elsewhere. Each optimization I run is stand-alone; therefore, each set of results needs to be copied over to its own new column. Right now my code copies over only the LAST set of results to all the new columns sequentially - instead of copying each set of results to its own new column. Please see below:

Dim j As Integer
Dim x As Range
Set x = Range(Sheets("SuperBell").Cells(34, 15), Sheets("SuperBell").Cells(52, 15))

Dim i As Integer

For i = 34 To 52
SolverReset
SolverOk SetCell:="$Q$" & i, MaxMinVal:=3, ValueOf:="1", ByChange:="$O$" & i
SolverSolve (True)
Next i

x.Copy
Dim y As Range
For j = 19 To 21

Set y = Range(Sheets("SuperBell").Cells(10, j), Sheets("SuperBell").Cells(28, j))
y.PasteSpecial Paste:=xlPasteValues

Next j


Now, my j needs to ultimately loop 300 times, rather than 3, but you get the idea. I have the feeling I am making a very basic mistake somewhere, but at this point - beats me! I also browsed this forum for a while, but could not quite get at what I needed... So any and all help would be GREATLY appreciated. Thanks in advance!!

Best Regards,
-Nick N.

 

[BIGTONE559]

Couldn't you use x.copy Destination:=y

just as long as you declare everything before hand?

 

[Legacy 234512]

i havent checked if this works ( it should tho ) - check if this is what youre looking for

' arg1: worksheet to copy to
' arg2: column
Sub copyRanges(wS As Worksheet, copyTo As String)

    Sheets("SuperBell").Select
x.Select
    Selection.Copy
    Sheets(wS.name).Select
    Range(copyTo).Select

    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

 

[nixon72]

Guys, thanks for the feedback - but the problem is with the loop itself, i.e. how to capture the results every time Solver iterates. Solver produces a vector (column) of results, which needs to be saved somewhere; then it produces another set of results, which also needs to be saved, and so on...

 

[Legacy 234512]

create a range variable
select range and set it to created variable
now your created object refers to the selection?
what else do you want to do cause i may be misunderstanding your question

 

[nixon72]

OK, thanks for sticking with me! I think it would be best if I posted the whole section of code - since it's 3 nested loops. Briefly, this is what I need to do:

1) My sheet gets data from another sheet repeatedly (Loop 1)
2) Then I run a Solver model iteratively to solve a bond math problem (Loop 2)
3) Finally, each vector (column) of Solver solutions needs to be value-pasted in its own column (Loop 3). And this is the step where grief catches up with me.

Both t and j need to loop around 300 times; whereas i is fine as it is. Hopefully this will clarify things. Full code follows below: THANKS again!!

Sub Looper()

Dim t As Integer
Dim j As Integer
Dim i As Integer
Dim x As Range
Set x = Range(Sheets("SuperBell").Cells(34, 15), Sheets("SuperBell").Cells(52, 15))

For t = 3 To 4

Sheets("YC").Select
Range(Sheets("YC").Cells(t, 3), Sheets("YC").Cells(t, 21)).Select
Selection.Copy
Sheets("SuperBell").Select
Range(Sheets("SuperBell").Cells(10, 2), Sheets("SuperBell").Cells(28, 2)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True

Sheets("CC").Select
Range(Sheets("CC").Cells(t, 2), Sheets("CC").Cells(t, 20)).Select
Selection.Copy
Sheets("SuperBell").Select
Range(Sheets("SuperBell").Cells(10, 3), Sheets("SuperBell").Cells(28, 3)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True


Sheets("SuperBell").Cells(34, 15).Select
ActiveCell.FormulaR1C1 = "=RC[-13]"
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown

For j = 1 To 3

For i = 34 To 52
SolverReset
SolverOk SetCell:="$Q$" & i, MaxMinVal:=3, ValueOf:="1", ByChange:="$O$" & i
SolverSolve (True)
Next i

With x
.Offset(-24, j + 3).Value = .Value
End With

Next j

Next t

End Sub