VBA code needed to loop through all combinations of input data sets.

[peterinaudo]

Hi, thankyou for reading my post.<o></o>
<o></o>
I am using Excel 2013 32bit on a 64bit system running Windows7. <o></o>
I was not able to get a HTML maker to work with Excel 2013 so I have copied and pasted the cells directly from the spread sheet. I am sorry for the inconvenience this causes.<o></o>
<o></o>
Problem:<o></o>
This sample spread sheet shows 3 Entry Data sets – Entry A, Entry B, and Entry C, and 3 Exit data sets – Exit X, Exit Y, and Exit Z.<o></o>
I would like help with some VBA code to automatically loop through all of the 9 combinations of Entries and Exits. This loop code needs to be added to the existing VBA code shown below.<o></o>
<o></o>
Existing setup:<o></o>
Sheet 1(Data) has the 6 different Data sets, 3 sets for Entry levels and 3 sets for Exit levels. (There will be many more Entry and Exit data sets as the spread sheet is developed) (shown below)<o></o>
The word 'Date' is in cell A2

Date	Entry A	Entry B	Entry C		Exit X	Exit Y	Exit Z
1/1/2010	6.5
1/2/2010		7					8
1/3/2010			9
1/4/2010					5	6
1/5/2010							7
1/6/2010			10		7	7
1/7/2010	6.7
1/8/2010		8
1/9/2010							6.5
1/10/2010	7
1/11/2010			8		5.5	4
1/12/2010							9

<tbody>
</tbody>

Sheet 2(Calcs) is a calculation sheet. (shown below)<o></o>
This sheet is where each data set is to be inserted at C3 and D3, then the VBA program shown below is run. This VBA program calculates the end result of the particular combination of Entry and Exit data and produces the Total Result in a summary line.<o></o>
The word 'Date is in cell A4.

				Total Result
		Entry A	Exit X	-$410
Date					Entry Date	Entry price	Exit Date	Exit Price	Units Bought	Position Size	Result
1/1/2010		6.5			1/01/2010	6.5	1/04/2010	5	154	$1,000	-$231
1/2/2010					1/07/2010	6.7	1/11/2010	5.5	149	$1,000	-$179
1/3/2010
1/4/2010			5
1/5/2010
1/6/2010			7
1/7/2010		6.7
1/8/2010
1/9/2010
1/10/2010		7
1/11/2010			5.5
1/12/2010

<tbody>
</tbody>

The formula in the first cell of the Units Bought column is =IF(G5="","",K5/G5) and copied down.
<o>The formula in the first cell of the Position Size column is =IF(F5<>"",1000,"") and copied down</o>
The formula in the first cell of the Result column is =IF(I5="","",(J5*(I5-G5))) and copied down
The formula in the Result Total cell is =SUM(L5:L400)




Sheet 3(Summary) is a Summary sheet.<o></o>

As well as executing the calculations, the VBA program also copies the summary line from the Calcs sheet showing the name of the Data sets used and the Total Result and pastes them to this summary sheet.<o></o>


This is the blank Summary sheet before any of the calculation VBA code is run. The highest empty cell in Column A needs to be left as the active cell before leaving this sheet as this is where the next row of summary data will be pasted. <o></o><o></o>

Entry A	Exit X	Total Result

<tbody>
</tbody>

The cellsbelow show what the Summary sheet should look like after all of thecombinations of Entry and Exit are calculated and pasted.

EntryData	Exit Data	Total Result
Entry A	Exit X	-$ 409.87
Entry A	Exit Y	-$ 479.91
Entry A	Exit Z	$ 486.63
Entry B	Exit X	-$ 598.21
Entry B	Exit Y	-$ 642.86
Entry B	Exit Z	-$ 187.50
Entry C	Exit X	-$ 894.44
Entry C	Exit Y	-$ 933.33
Entry C	Exit Z	-$ 447.22

<tbody>
</tbody>

This is theVBA code I am currently running to do the calculations and paste the results tothe summary page.<o></o>

<o></o>

<o></o>

<o></o>

<!--[if !supportLineBreakNewLine]-->
<!--[endif]--><o:p>Option Explicit</o:p>

<o:p>
Sub Calculate_Combinations_v4()
'
'This macro calculates the result of the entered Entry and Exit Data columns
' and sends the result to the table on the Summary sheet.
'
'The original version of this macro was kindly set up for me by Pete_SSs on MrExcel Forum.
'Peter also very kindly wrote very detailed descriptions for each line of code so I
'could follow and change the code to suit my final spreadsheet set up.
'
'
'
'
'
'Clear the old TPS table results (recorded with macro recorder and added to Peter_SSs code)
    Range("F5:I5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("K3").Select
 
  Dim Data, Result, aCols, aRws
  Dim i As Long, k As Long, rws As Long, LastRow As Long
  Dim oSet As Double
  
  Const Date_Data1_Data2_Cols As String = "1 3 4"   'ie cols A, C & D
  Const FirstRow As Long = 5              'First row of actual data
  Const ResultTopLeft As String = "F5"    'Where Results should start
  
  'Make an array of the column numbers of interest. ie 1, 3, 4
  aCols = Split(Date_Data1_Data2_Cols)
  
  'Determine the last row of data by using the Date column,
  'now the first element in the aCols array
  LastRow = Cells(Rows.Count, CLng(aCols(0))).End(xlUp).Row
  
  'Make an array of row numbers for data area.
  'ie FirstRow, FirstRow+1, FirstRow+2, ...  , LastRopw
  aRws = Evaluate("row(" & FirstRow & ":" & LastRow & ")")
  
  'Read all data rows (but only the columns of interest) into an array
  Data = Application.Index(Columns("A").Resize(, aCols(2)), aRws, aCols)
  
  'Set the initial offset
  oSet = -0.5
  
  'Determine how many rows of data
  rws = UBound(Data, 1)
  
  'Prepare the Results array
  ReDim Result(1 To rws, 1 To 4)
  
  'Work through the Data array, swapping columns by changing the offset
  'from -0.5 to 0.5 each time data is encountered in the current column
  For i = 1 To rws
    'If data is encountered in the column
    If Data(i, 2.5 + oSet) <> "" Then
      'If oSet is negative then we move to a new Result row
      If oSet < 0 Then k = k + 1
      'Put the Date & Value into Results array
      Result(k, 2 + 2 * oSet) = Data(i, 1)
      Result(k, 3 + 2 * oSet) = Data(i, 2.5 + oSet)
      'Swap columns by negating the offset
      oSet = -oSet
    End If
  Next i
  
  'Write the Results to the sheet
  Range(ResultTopLeft).Resize(k, 4).Value = Result
  
  
' The following code was recorded with the macro recorder then inserted by me
'here at the bottom the code that Pete_SSs wrote.
'
'
'
' Copy the result of the test and Special Paste it to the table on the Summary sheet.
'
    Range("C3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Summary").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(1, 0).Range("A1").Select
    Sheets("Calcs").Select
    Application.CutCopyMode = False
    Range("K3").Select
    
End Sub
 
</o:p>

<o></o>

<o></o><o></o>

Data will be added each month so the number of rows will grow each month. <o></o>

If you are able to help me with code to do this I would appreciate it if you could also add the baby step comment lines explaining what each line of code does so I can learn as well as make small changes if needed to suit my final spread sheet.<o></o>