Solved help with excel currency conversions

Microsoft Office excel 2007
August 20, 2012 at 16:47:42
Specs: Windows 7
I am doing an expense report where some of quantities in the column will be dollars and some will be in haitian gourdes. I want all the amounts in the column to be in dollars. Can I make a formula/setting where any time I enter an amount in gourdes it converts automatically to dollars? I want my dollar amounts to be left alone and my gourde entries, perhaps indicated by a G after the amount, to be divided by 40.

See More: help with excel currency conversions

Report •

#1
August 20, 2012 at 17:30:09
✔ Best Answer
1 - Right click the sheet tab for the sheet you want this to happen in.
2 - Choose View Code.
3 - Paste the code below into the pane that opens.

If you enter a number in Column A that ends in g or G, the numerical portion will be divided by 40 and the g/G will be discarded. Text strings will be ignored.

e.g.

440g will return 11
56G will return 1.4
Fig will return Fig

Private Sub Worksheet_Change(ByVal Target As Range)
'Continue code if value not divisible by 40
 On Error Resume Next
'Determine if change was to Column A
  If Target.Column = 1 Then
'Determine if right-most character is g or G
   If UCase(Right(Target, 1)) = "G" Then
'If TRUE, divide numerical portion by 40
     Application.EnableEvents = False
      Target = Left(Target, Len(Target) - 1) / 40
     Application.EnableEvents = True
   End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 20, 2012 at 19:05:44
Thank you so much for your help. After I paste into the visual basic window what do I do? save to my file name then close the visual basic window? it is not working. . . Also my inputs are in column I so i just changed A to I.


Report •

#3
August 20, 2012 at 19:36:33
The A you probably changed was in the green Comment line, which won't do anything.

What you need to do is change the 1 to a 9 in the actual instruction line.

If Target.Column = 9 Then

Column A is 1, B is 2, etc.

Once you edit that line, you should be able to enter your values in Column I and have them change.

Eventually, the file will need to be saved as a .xlsm to retain the macro, but it doesn't have to be saved in order for it to work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 20, 2012 at 20:04:01
got it, thanks a lot!

Report •

#5
August 21, 2012 at 07:41:21
Do I have to open the visual basic window every time i want the macro to activate? I tried reopening my file and entering a G amount and the conversion did not manifest.

Report •

#6
August 21, 2012 at 07:53:01
There is no need to open the VBA editor each time...that would be pretty inconvenient.

Did you save the file as an .xlsm file?

Do you try the conversion in the same sheet in which you pasted the code yesterday? Right-click the sheet tab, choose view-code and make sure the code is still there.

The last thing to check would be your Macro Security setting.

In 2003 it's Tools...Macro..Security

In 2007, I can't tell you the steps because I don't have 2007 on the machine I'm currently sitting at.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
August 21, 2012 at 10:56:20
what do you suggest for the security setting?

Report •

#8
August 21, 2012 at 12:54:01
I have mine set for Low since I have other security software and firewalls running. I don't worry about Excel macros infecting my system since I am careful about what I download and rely on the security software to catch stuff.

Any other setting will require input from you, such as agreeing to allow a macro to run each time you open the workbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question