PDA

View Full Version : Solved: Code Help for FX conversion and formatting



Uller
03-20-2009, 12:57 PM
Hey guys,

I'm still fairly new to VBA, and I am having some issues with the following: I am trying to design a macro that will convert inputted fx values to USD when the user enters the conversion number.

I need a new column to the left of the "Country Code" column with the converted values calculated for each total in the "Summation" column (it is just multiplying the summation column values by the inputted number). Here is the code that I have so far that finds the country currency and prompts the user to enter a conversion rate (for this case AUD into USD).

Sub CurrencyFX()
'

Cells.Find(What:="Customer Currency", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(1, 0).Select
If Selection <> "USD" Then
ActiveCell.EntireColumn.Insert
UserVal = Application.InputBox(Prompt:="Currency conversion factor?", Type:=1)

End If

I have attached the file, please let me know if you have any suggestions! I would greatly appreciate it!

Regards,

Uller

mdmackillop
03-20-2009, 02:36 PM
Try this.

Uller
03-20-2009, 03:06 PM
Hey MD! That was above and beyond my plan for this. Thanks a lot! I wanted to pick your brain a bit on the code..

Did the code included on this actually populate the currency column with the USD values or did you enter those manually? I didnt see any reference to them so just wondering.

Definitely speeding up the learning curve...

Uller

mdmackillop
03-20-2009, 03:14 PM
There is code in ThisWorkbook module to download currency rates once a day when the book is first opened (gethtmltable) In Sheet 2, delete the date in F1 and Cols B-D to test this.
The button on the userform (see the userform code module) looks up the listbox currency in this table, applies a factor (5%) and loops through the priced cells putting the calculated total in the cell offset.
Please check that the result are correct. I wasn't too sure what conversion you were after.

Uller
03-20-2009, 04:44 PM
Everything checked out. Code was very clear and concise. No adjustment factor (5%) was needed, but this will speed up currency adjustments in the future.

Cheers

Uller

mdmackillop
03-20-2009, 04:47 PM
Glad to help. The factor was because Buy/Sell/Retail rates are not the same as "official" exchange rates and allows you to adjust for this.