Variance calculation between values from separate files

[gigi87]

Hi all,
I am beginner in Excel VBA, just starting to learn it and I would really need some help with the below problem. I am struggling to make it work with no success so far…
I am starting with 3 separate files (one having an amount of today, the second an amount of yesterday and the third is a condition that has to be checked) and I want to generate a new file that would show the variance as percentage between the 2 amounts, today and yesterday, based on the threshold limit from the 3^rd file.
My files would look like this:
1) File as of Day-1 (around 25.000 rows)

Column A	Column B	Column C		Column N
Shareclass	Fund	Client Code	…….	Amount

<tbody>
</tbody>

2) File as of Day (around 25.000 rows)

Column A	Column B	Column C		Column N
Shareclass	Fund	Client Code	…….	Amount

<tbody>
</tbody>

3) In addition I have a third file with two worksheets:
- Worksheet (“Thresholds”)

Column A	Column B	Column C
Client Name	Client Code	Threshold

<tbody>
</tbody>

- Worksheet (“MMI”)

Column A	Column B
Fund	MMI

<tbody>
</tbody>

I would like to generate a file that shows the below results:
In the first worksheet will have the content of “file as of Day-1” except the client code “ABC”,“CDB” and except the funds “ab12345”, “fb45546” and “hj4564”.
In the second worksheet will have the content of “file as of Day” except the client code “ABC”,“CDB” and except the funds “ab12345”, “fb45546” and “hj4564”.
Third worksheet :
[TABLE="width: 669"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column F
[/TD]
[TD]Column G
[/TD]
[/TR]
[TR]
[TD]Shareclass
[/TD]
[TD]Fund
[/TD]
[TD]Client Code
[/TD]
[TD]Amount D-1
[/TD]
[TD]Amount D
[/TD]
[TD]Variance
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]%
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
The column F will show the variance between the amount as percentage (at shareclass level):
- if “amount D” if equal to zero or empty and “amount D-1”>0, the variance will be -100%
- if “amount D-1” if equal to zero or empty and “amount D”>0, the variance will be +100%
The column “comments” will have three values:
- “Above threshold” if the variance is greater than the threshold defined at client level
- “Below threshold” if the variance is lower than the threshold defined at client level
- “MMI” if the fund is classified as MMI
Many thanks for your help