VBA help

[Emmily]

Hi, is there a macro that will produce the below result. For each team i want the count of entries per age criteria as reflected in row 1 and then i want the amount in (AUD) and then the number of entries which does not have a comment in col P and it value in AUD.

I know all of this can be done via formula, but i will have another 20 teams and the last age criteria will go to >90, so there will be a lot of formulas, hence it will slow the performance of the workbook.

Col L-P is raw data and R-S is FX data

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		2-5				6-29
2	Team	No.of items	Value (AUD)	No. of items without Comments	Value (AUD)	No.of items	Value (AUD)	No. of items without Comments	Value (AUD)			Amount	CCY	Age	Source	Comments		FX RATES	Rate (AUD)
3	CMP1	1	11,593.66	1	5,796.83	0	0.00	0	0.00			8,268.57	AUD	1456	TRACES			AED	3.712435
4	TRACES											20,000.00	AUD	1248	TRACES			ANG	1.809243
5	LEHMAN											1,000.00	AUD	1248	TRACES			ARS	4.053867
6												4,417.80	GBP	3	CMP1	Test		ATS	10.203373
7												2,208.90	GBP	1018	CMP1			AUD	1
8												100,000.00	AUD	742	TRACES			BEF	29.912398
9												1,163.22	EUR	561	LEHMAN			BGN	1.450275
10												-3,320.14	EUR	561	LEHMAN			GBP	0.381053
11												9,646.51	EUR	561	LEHMAN	Test		BMD	1.01075
12												-12,618.80	EUR	561	LEHMAN			BRL	1.688256
13												476,017.76	EUR	561	LEHMAN			BSD	1.01075
14												-1,688,137.00	JPY	561	LEHMAN			BVD	2
15												7,187,517.00	JPY	561	LEHMAN			CAD	1.001098
16												-6,303,971.00	JPY	561	LEHMAN			CHF	0.957838
17												108,431.34	USD	561	LEHMAN
18
19
20
Sheet1

 

[Krishnakumar]

Hi,

Forget about StartRow. I assume that the defined name includes the header row. If so, the StartRow would be 2 otherwise it's 1.

I Dim startrow, because earlier I was taking the usedrange but now the range is defined. Use either 1(no header) or 2 (include header)

 

[Emmily]

Hi,

Try something like

k(t, Idx * 4 - 4 + 3) = application.round(k(t, Idx * 4 - 4 + 3) + ka(i, 11),2)

Thanks Kris Starrow 2 worked. With the rounding is this correct for the cash part cos what you showed up is for the asset. I did try and run including the below but i can still see >2dp

k(t, Idx * 4 - 4 + 5) = Application.Round(k(t, Idx * 4 - 4 + 5), 2) + dblAmount / dic1.item(strCurrency)

 

[Krishnakumar]

Hi

k(t, Idx * 4 - 4 + 5) = Application.Round(k(t, Idx * 4 - 4 + 5) + dblAmount / dic1.item(strCurrency), 2)

 

[Emmily]

Hi

k(t, Idx * 4 - 4 + 5) = Application.Round(k(t, Idx * 4 - 4 + 5) + dblAmount / dic1.item(strCurrency), 2)

Sweet, thanks Kris.