Arrays

[Guest]

I have a list of transactions that i would like to ditribute to a column
named volume . The transactions have a start date, a enddate, a portfolio
name, the volume and a price. The volumes are to be distributed on the dates
that fits to the transactions. ie. if a transactions have a start date of
2005/1/1 and a endate of 2005/1/31 i would like to have the volume
distributed on the dates that i have in column a, based on the portfolio. The
transactions look like below, and the list that i would like to create is
below the transactions.

I have tried to use arrays, but i cant really figure out how to do this.
Does anyone have any suggestions on how to do this? I have done it with
formulas, but as I have thousands of transactions it takes ages to calculate.

Portfolio startdate Enddate Volume Price
Portfolio A 01.01.05 31.01.05 5 32


Portolio A Portfolio B

Volume Volume
01.01.05 5 0
02.01.05 5 0
03.01.05 5 0
04.01.05 5 0

 

[Guest]

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

I have tried sumproduct, but I have so many portfolios and "dates" that it
takes ages to calculate.

 

[Tom Ogilvy]

Option Explicit
Sub Tester2()
Dim dt1 As Date, dt2 As Date
Dim dblVol As Double
Dim lNum As Long, k As Long
Dim varr() As Variant
dt1 = CDate("1/1/2005")
dt2 = CDate("1/31/2005")
lNum = CLng(dt2 - dt1 + 1)
dblVol = 5
ReDim varr(1 To lNum, 1 To 3)
For k = 1 To lNum
varr(k, 2) = dblVol
varr(k, 3) = 0
varr(k, 1) = dt1 + (k - 1)
Next

For k = 1 To lNum
Debug.Print Format(varr(k, 1), "dd.mm.yy") & " " _
& varr(k, 2) & " " & varr(k, 3)
Next

End Sub

produced:
01.01.05 5 0
02.01.05 5 0
. . .
30.01.05 5 0
31.01.05 5 0

 

[Frank Kabel]

Hi
this is IMHO the only option if you really need a dynamic solution.
Otherwise you have to use a pivot table or a macro (e.g. the one Tom
provided)