VBA to revalue financial statement

[Certified]

Hi,

On one sheet (sheet 1) I have A financial statement separated by currency.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]EUR[/TD]
[TD]HKD[/TD]
[TD]PLN[/TD]
[TD]USD[/TD]
[/TR]
[TR]
[TD]0005 cash[/TD]
[TD]590[/TD]
[TD]2601[/TD]
[TD]62563[/TD]
[TD]2427024[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

on another sheet (Sheet 2) I have month end exchange rates.


[TABLE="width: 500"]
<tbody>[TR]
[TD]from[/TD]
[TD]to[/TD]
[TD]date[/TD]
[TD]fx rate[/TD]
[/TR]
[TR]
[TD]EUR[/TD]
[TD]USD[/TD]
[TD]06/30/2014[/TD]
[TD]1.36925[/TD]
[/TR]
[TR]
[TD]HKD[/TD]
[TD]USD[/TD]
[TD]06/30/2014[/TD]
[TD].12902
[/TD]
[/TR]
[TR]
[TD]PLN[/TD]
[TD]USD[/TD]
[TD]06/30/2014[/TD]
[TD].32816[/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD]USd[/TD]
[TD]06/30/2014[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I am tying to produce on a new balance sheet would look like this...

Account Amount
005 Cash 244,698, which is 590 *1.36925, 2601 * .12902, 62563 * .32816 and 2427024 *1.00

FYI, I have close to 200 accounts.

I am new at VBA and I am stuck on how I should approach this task.

Any help is greatly appreciated.

 

[skywriter]

Excel 2010
	A	B	C	D	E	F	G
1	Account	EUR	HKD	PLN	USD		Sum
2	0005 cash	807.8575	335.58102	20530.67408	2427024		2448698
New Balance Sheet

Cell Formulas
Range	Formula
A2	=Sheet1!A2
B2	=INDEX(Sheet1!$B:$E,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$B$1:$E$1,0))*VLOOKUP(B$1,Sheet2!$A:$D,4,0)
C2	=INDEX(Sheet1!$B:$E,MATCH($A2,Sheet1!$A:$A,0),MATCH(C$1,Sheet1!$B$1:$E$1,0))*VLOOKUP(C$1,Sheet2!$A:$D,4,0)
D2	=INDEX(Sheet1!$B:$E,MATCH($A2,Sheet1!$A:$A,0),MATCH(D$1,Sheet1!$B$1:$E$1,0))*VLOOKUP(D$1,Sheet2!$A:$D,4,0)
E2	=INDEX(Sheet1!$B:$E,MATCH($A2,Sheet1!$A:$A,0),MATCH(E$1,Sheet1!$B$1:$E$1,0))*VLOOKUP(E$1,Sheet2!$A:$D,4,0)
G2	=SUM(B2:E2)

Excel 2010
	A	B	C	D	E
1	Account	EUR	HKD	PLN	USD
2	0005 cash	590	2601	62563	2427024
Sheet1

Excel 2010
	A	B	C	D
1	from	to	date	fx rate
2	EUR	USD	6/30/2014	1.36925
3	HKD	USD	6/30/2014	0.12902
4	PLN	USD	6/30/2014	0.32816
5	USD	USd	6/30/2014	1
Sheet2