Code:
[FONT=Arial]Function swapfx(startdate As Double, enddate as Double) As Double<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]Dim i As Double<o:p></o:p>[/FONT]
[FONT=Arial]Dim currmo As Double<o:p></o:p>[/FONT]
[FONT=Arial]Dim currmojump As Double<o:p></o:p>[/FONT]
[FONT=Arial]Dim price As Double<o:p></o:p>[/FONT]
[FONT=Arial]Dim pricejump As Double<o:p></o:p>[/FONT]
[FONT=Arial]Dim space As Double<o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]space = (WorksheetFunction.Year(enddate) - WorksheetFunction.Year(startdate)) * 12 _<o:p></o:p>[/FONT]
[FONT=Arial]+ (WorksheetFunction.Month(enddate) - WorksheetFunction.Month(startdate))<o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]currmo = startdate<o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]Activate.Range ("firstnot")[/FONT][FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]For i = 1 To space<o:p></o:p>[/FONT]
[FONT=Arial] If currmonth <= Value.ActiveCell Then<o:p></o:p>[/FONT]
[FONT=Arial] pricejump = Value.ActiveCell.Offset(0, 1) + price<o:p></o:p>[/FONT]
[FONT=Arial] price = pricejump<o:p></o:p>[/FONT]
[FONT=Arial] currmojump = WorksheetFunction.EDate(currmo, i)<o:p></o:p>[/FONT]
[FONT=Arial] currmo = currmojump<o:p></o:p>[/FONT]
[FONT=Arial] <o:p></o:p>[/FONT]
[FONT=Arial] Else:<o:p></o:p>[/FONT]
[FONT=Arial] ActiveCell.Offset(i, 1).Activate<o:p></o:p>[/FONT]
[FONT=Arial] pricejump = Value.ActiveCell.Offset(0, 1) + price<o:p></o:p>[/FONT]
[FONT=Arial] price = pricejump<o:p></o:p>[/FONT]
[FONT=Arial] currmojump = WorksheetFunction.EDate(currmo, i)<o:p></o:p>[/FONT]
[FONT=Arial] currmo = currmojump<o:p></o:p>[/FONT]
[FONT=Arial] End If<o:p></o:p>[/FONT]
[FONT=Arial]Next i<o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]price = pricejump / space<o:p></o:p>[/FONT]
[FONT=Arial]swapfx = price<o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]End Function<o:p></o:p>[/FONT]The purpose of this function is to find the average price over several months of contracts. The dates in the column below the Range "firstnot" are all contract expiry dates - so the function should take the price from "currmo" (current month) unless the currmo > the contract expiry date. It should end before the end date given the "space" variable.
The space variable is a way to measure the number of months between two dates in different years that I found on Office's help site.
pricejump and currmojump are simply variables so that I can add the price from all of the other months in the loop to the end price.
Any advice is much appreciated! Thanks very much!
