# VBA to revalue financial statement

#### Certified

##### Board Regular
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### skywriter

##### Well-known Member

Excel 2010
ABCDEFG
1AccountEURHKDPLNUSDSum
20005 cash807.8575335.5810220530.6740824270242448698
New Balance Sheet
Cell Formulas
RangeFormula
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
ABCDE
1AccountEURHKDPLNUSD
20005 cash5902601625632427024
Sheet1

Excel 2010
ABCD
1fromtodatefx rate
2EURUSD6/30/20141.36925
3HKDUSD6/30/20140.12902
4PLNUSD6/30/20140.32816
5USDUSd6/30/20141
Sheet2

Replies
5
Views
689
Replies
11
Views
996
Replies
1
Views
490
Replies
4
Views
515
Replies
0
Views
546

1,191,697
Messages
5,988,165
Members
440,132
Latest member
Excellearner253

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back