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

P

peterinaudo

New Member
Joined
Oct 4, 2012
Messages
25
Hi, thankyou for reading my post.<o:p></o:p>
<o:p></o:p>
I am using Excel 2013 32bit on a 64bit system running Windows7. <o:p></o:p>
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:p></o:p>
<o:p></o:p>
Problem:<o:p></o:p>
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:p></o:p>
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:p></o:p>
<o:p></o:p>
Existing setup:<o:p></o:p>
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:p></o:p>
The word 'Date' is in cell A2
[TABLE="width: 395"]
<tbody>[TR]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Date
[/TD]
[TD="class: xl65, bgcolor: transparent"]Entry A
[/TD]
[TD="class: xl65, bgcolor: transparent"]Entry B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Entry C
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Exit X
[/TD]
[TD="class: xl65, bgcolor: transparent"]Exit Y
[/TD]
[TD="class: xl65, bgcolor: transparent"]Exit Z
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/1/2010
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6.5
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/2/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/3/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/4/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/5/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/6/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/7/2010
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6.7
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/8/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/9/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6.5
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/10/2010
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/11/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5.5
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1/12/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2(Calcs) is a calculation sheet. (shown below)<o:p></o:p>
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:p></o:p>
The word 'Date is in cell A4.
[TABLE="width: 643"]
<tbody>[TR]
[TD="width: 74, bgcolor: transparent"][/TD]
[TD="width: 74, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 86, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl79, width: 62, bgcolor: white"]Total Result
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"]Entry A
[/TD]
[TD="class: xl76, bgcolor: transparent"]Exit X
[/TD]
[TD="class: xl75, bgcolor: transparent"]-$410
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]Date
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 78, bgcolor: transparent"]Entry Date
[/TD]
[TD="class: xl68, width: 86, bgcolor: transparent"]Entry price
[/TD]
[TD="class: xl67, width: 81, bgcolor: transparent"]Exit Date
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Exit Price
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Units Bought
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Position Size
[/TD]
[TD="class: xl70, width: 75, bgcolor: transparent"]Result
[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/1/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]6.5
[/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF, align: right"]1/01/2010
[/TD]
[TD="class: xl65, bgcolor: #CCCCFF, align: right"]6.5
[/TD]
[TD="class: xl71, bgcolor: #CCCCFF, align: right"]1/04/2010
[/TD]
[TD="class: xl65, bgcolor: #CCCCFF, align: right"]5
[/TD]
[TD="class: xl72, bgcolor: transparent"]154
[/TD]
[TD="class: xl73, bgcolor: transparent"]$1,000
[/TD]
[TD="class: xl74, bgcolor: transparent"]-$231
[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/2/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF, align: right"]1/07/2010
[/TD]
[TD="class: xl65, bgcolor: #CCCCFF, align: right"]6.7
[/TD]
[TD="class: xl71, bgcolor: #CCCCFF, align: right"]1/11/2010
[/TD]
[TD="class: xl65, bgcolor: #CCCCFF, align: right"]5.5
[/TD]
[TD="class: xl72, bgcolor: transparent"]149
[/TD]
[TD="class: xl73, bgcolor: transparent"]$1,000
[/TD]
[TD="class: xl74, bgcolor: transparent"]-$179
[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/3/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF"][/TD]
[TD="class: xl65, bgcolor: #CCCCFF"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF"][/TD]
[TD="class: xl65, bgcolor: #CCCCFF"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/4/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF"][/TD]
[TD="class: xl65, bgcolor: #CCCCFF"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF"][/TD]
[TD="class: xl65, bgcolor: #CCCCFF"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/5/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF"][/TD]
[TD="class: xl65, bgcolor: #CCCCFF"][/TD]
[TD="class: xl71, bgcolor: #CCCCFF"][/TD]
[TD="class: xl65, bgcolor: #CCCCFF"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/6/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/7/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]6.7
[/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/8/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/9/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/10/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/11/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]5.5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent, align: right"]1/12/2010
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
The formula in the first cell of the Units Bought column is =IF(G5="","",K5/G5) and copied down.
<o:p>The formula in the first cell of the Position Size column is =IF(F5<>"",1000,"") and copied down</o:p>
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:p></o:p>

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:p></o:p>


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:p></o:p><o:p></o:p>
[TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 77, bgcolor: transparent"]Entry A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Exit X
[/TD]
[TD="class: xl68, width: 78, bgcolor: white"]Total Result
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

The cellsbelow show what the Summary sheet should look like after all of thecombinations of Entry and Exit are calculated and pasted.
[TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 77, bgcolor: transparent"]EntryData
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Exit Data
[/TD]
[TD="class: xl68, width: 78, bgcolor: white"]Total Result
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry A
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit X
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 409.87
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry A
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit Y
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 479.91
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry A
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit Z
[/TD]
[TD="class: xl69, bgcolor: transparent"] $ 486.63
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry B
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit X
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 598.21
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry B
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit Y
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 642.86
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry B
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit Z
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 187.50
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry C
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit X
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 894.44
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry C
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit Y
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 933.33
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Entry C
[/TD]
[TD="class: xl67, bgcolor: transparent"]Exit Z
[/TD]
[TD="class: xl69, bgcolor: transparent"]-$ 447.22
[/TD]
[/TR]
</tbody>[/TABLE]


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

<o:p></o:p>

<o:p></o:p>

<o:p></o:p>

Rich (BB code): 
<!--[if !supportLineBreakNewLine]-->
<!--[endif]--><o:p>Option Explicit</o:p>
Rich (BB code): 
<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:p></o:p>

<o:p></o:p><o:p></o:p>

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

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:p></o:p>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You must log in or register to reply here.

Similar threads

M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
387
maiwarits
M
A
  • Solved
why the code is slow despite of using advanced filter with simple data
2 3
Replies
23
Views
404
abdelfattah
A
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
594
mumps
mumps
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
262
Kaper
Kaper
D
  • Question Question
VBA code that loops through a range and copies and pastes the data below
Replies
2
Views
305
danbates77
D

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top