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,

That's because of null value. Anyway replace that line with

dblAmount = CDbl(Trim$(ka(i, 7) + 0))

 

[Emmily]

Hi,

That's because of null value. Anyway replace that line with

dblAmount = CDbl(Trim$(ka(i, 7) + 0))

i get the same error here, do i just do this

lngAge = Trim$(ka(i, 9)+0)

 

[Krishnakumar]

If age col is empty then what would you like to do. Skip the row or ??

 

[Emmily]

age col will never be empty within the data, but i tried this and it worked. When you say empty do you mean empty rows up the header row?

lngAge = Trim$(ka(i, 9) + 0)

 

[Krishnakumar]

No, I mean the row where age is empty.

 

[Emmily]

No, I mean the row where age is empty.

If age row is empty then i would prefer to address the user of the cell address of the empty cell via msgbox

 

[Krishnakumar]

Hi,

Try something like

If Len(Trim$(ka(i, 9))) = 0 Then
            MsgBox "Age is missing in '" & Sheets(SourceShtName).Range(SourceDataRange).Cells(i, 9).Address(0, 0) & "' on " & SourceShtName, vbInformation
            Exit Sub
        Else
            lngAge = Trim$(ka(i, 9))
        End If

 

[Emmily]

Awesome, this works. I did a test on full data and results look good. Thanks for your help on this.

Hi,

Try something like

If Len(Trim$(ka(i, 9))) = 0 Then
            MsgBox "Age is missing in '" & Sheets(SourceShtName).Range(SourceDataRange).Cells(i, 9).Address(0, 0) & "' on " & SourceShtName, vbInformation
            Exit Sub
        Else
            lngAge = Trim$(ka(i, 9))
        End If

 

[Krishnakumar]

Awesome, this works. I did a test on full data and results look good. Thanks for your help on this.

You are welcome !!

 

[Emmily]

You are welcome !!

Kris, i noticed if a team is not in rng Source but its reflected in Sheet1 then the code removes this team from Sheet1. The teams in sheet1 must not be removed even if the team is not found in rngSource as there might not always be data available for those teams.