ThePangloss
New Member
- Joined
- Jun 19, 2015
- Messages
- 40
Hey so I'm trying to make something in VBA that would make you input current payment and additional monthly, as well as select a range of a column that consists of FX rates. Then enter in the current month and calculate the information up to then and post it in this kind of format. It's going to be used for the future so I imagine the next time it's updated would be Jul 16 so the person would just find the FX rates and put them in the sheet and have it be able to select all the new rates.
At the end it should take all the values it's calculated and paste them into the rows below, so do the same for every row and I imagine I could do just the formula for one row and then have it do Next.
It looks something like this:
<tbody>
</tbody>
Recieved is Paid*FX for that month + additional monthly
Shortfall is Theoretical - (Paid*FX for that month) - Additional Monthly
Cumulative variance is just the shortfalls added to each other from the previous shortfall.
I'm not exactly sure how to do this but I would guess it involves using Dim for 7 variables. Three of them would be as long, which are the additional monthly, theoertical and the actual payment. One as range which would be the set of the current FX rates up to whatever the current date is. Recived, Shortfall, and Cumulative shortfall would be as variable. I don't know how to put input boxes to enter information so I'm quite stumped on that part.
I'd prefer to do this than use formulas because there are multiple sheets like this, and it gets really annoying after a while. Some sheets are organized differently, or have extra columns with more payments and more FX changes, so if I have a general code for this I feel like modifying it would make this a lot easier.
Any help would be appreciated! Thanks
At the end it should take all the values it's calculated and paste them into the rows below, so do the same for every row and I imagine I could do just the formula for one row and then have it do Next.
It looks something like this:
| Month | Paid Amount | Theoretical | US/Uk Conversion | Additional Monthly | Recieved | Shortfall | Cumulative Variance |
| Oct-14 | 4421.25 | 8669.76 | 1.60680 | 1216.52 | 8320.59 | 349.17 | -1612.30 |
| Nov-14 | 4421.25 | 8669.76 | 1.57695 | 1216.52 | 8188.62 | 481.14 | -1131.17 |
| Dec-14 | 4421.25 | 8669.76 | 1.56315 | 1216.52 | 8128.60 | 542.16 | -589.00 |
| Jan-15 | 4421.25 | 8669.76 | 1.51568 | 1216.52 | 7917.72 | 752.04 | 163.04 |
| Feb-15 | 4421.25 | 8669.76 | 1.53274 | 1216.52 | 7993.15 | 676.61 | 839.65 |
<tbody>
</tbody>
Recieved is Paid*FX for that month + additional monthly
Shortfall is Theoretical - (Paid*FX for that month) - Additional Monthly
Cumulative variance is just the shortfalls added to each other from the previous shortfall.
I'm not exactly sure how to do this but I would guess it involves using Dim for 7 variables. Three of them would be as long, which are the additional monthly, theoertical and the actual payment. One as range which would be the set of the current FX rates up to whatever the current date is. Recived, Shortfall, and Cumulative shortfall would be as variable. I don't know how to put input boxes to enter information so I'm quite stumped on that part.
I'd prefer to do this than use formulas because there are multiple sheets like this, and it gets really annoying after a while. Some sheets are organized differently, or have extra columns with more payments and more FX changes, so if I have a general code for this I feel like modifying it would make this a lot easier.
Any help would be appreciated! Thanks
