VBA to revalue financial statement

[Certified]

Hi,

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

Account	EUR	HKD	PLN	USD
0005 cash	590	2601	62563	2427024

<tbody>
</tbody>

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

from	to	date	fx rate
EUR	USD	06/30/2014	1.36925
HKD	USD	06/30/2014	.12902
PLN	USD	06/30/2014	.32816
USD	USd	06/30/2014	1.00

<tbody>
</tbody>

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