Looping test bed for 3 variables

[RINCONPAUL]

After some code to loop copy subsets (identified in col A) of values in col B, paste it into col I, wherein the formulas in cols H, J & K (which react to the three variables in Range N1:N3), calculate a result that appears in col H. The total of that result is G1 and that is pasted consecutively after each loop in col S. Col I is cleared, and the loop repeats till no data left.

There are 4 subsets in this example and the results for the 4 loops appear in col S with a total in cell T1.

Background: This is a test bed to rapidly test to see the outcome result, as displayed in cell T1, of setting 3 variables as stop loss/profit variables used in trading. The chart is a bonus, but not necessary for the test bed, as the X & Y axis range is always changing and would need to be dynamic. Probably rob time off the loop process, as each test run would comprise thousands of subsets.

Cheers, as always for your interest.

[mdmackillop]

Where are you having trouble with your code? None is included in your workbook.

[RINCONPAUL]

Afraid my skill set hits a glass ceiling after "Record macro" mdmackillop! Here's the recording, how you loop and make the range variable and paste to next row etc.....:ack:

Sub Macro1()
'
' Macro1 Macro
'


'
Range("B2:B26").Select
Selection.Copy
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Range("S2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I2:I26").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

[mdmackillop]

A couple of methods
1. Record your macro using Find to locate the first and last occurrences of 1,2 3 etc and create a range from the results
2. Use Autofilter and F5 to select ranges to copy.

[RINCONPAUL]

You lost me, on those suggestions mdmackillop, however I've been taking baby steps and just trying to get one step right, before moving to next. In this code I manually enter a value for the Identity value in cell C1. Then run code which should copy all Ticks values attributed to that value, which it does, but then keeps copying and pasting parts of the list....weird. I can't see why it should do that?


Sub finddata()
Dim Identity As String
Dim finalrow As Integer
Dim i As Integer
Sheets("Tutorial (2)").Range("H1:I150").ClearContents
Identity = Sheets("Tutorial (2)").Range("C1").Value
finalrow = Sheets("Tutorial (2)").Range("A100").End(xlUp).Row


For i = 2 To finalrow
If Cells(i, 1) = Identity Then
Range(Cells(i, 1), Cells(1, 2)).Copy
Range("H100").End(xlUp).Offset(, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
End Sub


Even if I get that to work, it still requires manually entering thousands of values in C1.:crying:
When I Auto Filter, it leaves only the applicable rows, which stuffs up the adjacent paste area! Maybe the paste area should be on a separate worksheet?

[mdmackillop]

Here is a recorded macro using AutoFilter

Sub Macro4()
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$90").AutoFilter Field:=1, Criteria1:="1"
Columns("B:B").Select
Selection.Copy
ActiveSheet.Range("$A$1:$A$90").AutoFilter Field:=1
Range("I1").Select
ActiveSheet.Paste
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Range("S2").Select
ActiveSheet.Paste
ActiveSheet.Range("$A$1:$A$90").AutoFilter Field:=1, Criteria1:="2"
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Range("$A$1:$A$90").AutoFilter Field:=1
Columns("I:I").Select
ActiveSheet.Paste
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Range("S3").Select
ActiveSheet.Paste
ActiveSheet.Range("$A$1:$A$90").AutoFilter Field:=1, Criteria1:="3"
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Range("$A$1:$A$90").AutoFilter Field:=1
Range("I1").Select
ActiveSheet.Paste
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Range("S4").Select
ActiveSheet.Paste
End Sub

Examining this, it reduces to the following. There is an issue with preserving the copied data when removing the AutoFilter so the Variable is added to "remember" the visible cells


Sub DoCopy()
Dim r As Range
For i = 1 To 4
Range("A1:A500").AutoFilter Field:=1, Criteria1:=i
Set r = Range("B1:B500").SpecialCells(xlCellTypeVisible)
Range("A1:A500").AutoFilter Field:=1, Criteria1:="<>"
r.Copy Range("I1")
Range("G1").Copy Range("S1").Offset(i)
Next i
Range("A1:A500").AutoFilter
End Sub

[RINCONPAUL]

Nearly there mdmackillop, and again, you've blown me away with your shorthand coding!

I think it's a sequencing issue in that each time the filtered values are pasted to col "I", a new value is calculated in "G1". The split second calculation time might be too slow for the macro, as it pastes 0 for each criteria in col "S"? Dunno, appreciate it if you can have a look.

[mdmackillop]

Total the column in the code and add it to the appropriate cell.

[RINCONPAUL]

"Total the column in the code and add it to the appropriate cell", Now you've completely lost me. What does THAT mean?? Sorry

[mdmackillop]

Range("S1").Offset(i) = Application.Sum(Range("H:H"))

[RINCONPAUL]

OK...there's been a General Election overnight, and my brain's still getting over it. This works:

Sub DoCopy()
Dim r As Range
For i = 1 To 4
Range("A1:A500").AutoFilter Field:=1, Criteria1:=i
Set r = Range("B1:B500").SpecialCells(xlCellTypeVisible)
Range("A1:A500").AutoFilter Field:=1, Criteria1:="<>"
r.Copy Range("I1")
Range("G1").Value = WorksheetFunction.Sum(Range("H1:H50"))

Range("G1").Copy Range("S1").Offset(i)
Next i
Range("A1:A500").AutoFilter
End Sub

[mdmackillop]

There is no need for G1. Use the line above instead of

Range("G1").Value = WorksheetFunction.Sum(Range("H1:H50"))
Range("G1").Copy Range("S1").Offset(i)

[RINCONPAUL]

Yep, worked that out. Another mystery solved.

Great thanks as always. If time saved is money, then I'm a millionaire!.... with the fantastic help I've received from this Forum.

[RINCONPAUL]

"If time saved is money, then I'm a millionaire"....yes but will die of old age waiting for a run on one set of parameters to complete. Taking hours as my dataset is over 200,000 rows. That is still only a tiny sample of available data.

Is there anyway to speed up what seemed a very condensed piece of code?

[mdmackillop]

Try deleting the chart first.

[mdmackillop]

Are your J&K formula as shown? If not can you repost with the correct functions?