+ Reply to Thread
Results 1 to 10 of 10

Convert cell value to new currency using VBA

  1. #1
    Registered User
    Join Date
    02-27-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Convert cell value to new currency using VBA

    It would be great to get some assistance with the following please.

    In column A, I have currency code and columns B, C & D sales numbers. On a different worksheet, I have a currency conversion table. I would like to replace all the sales figures in columns B, C & D with the currency conversion based on the currency code in column A.

    In the example below on worksheet 1, all the values for EUR would decrease by 25% to convert them all to USD and the USD line would remain the same.

    For example:

    Worksheet1:
    Column A Column B Column C Column D
    EUR $100 $250 $200
    USD $100 $250 $200
    EUR $100 $250 $200


    Worksheet2:
    Column A Rate
    EUR 0.75
    USD 1.00

  2. #2
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Convert cell value to new currency using VBA

    Can you attach an example worksheet I dont understand by looking at that what you are trying to solve.

  3. #3
    Registered User
    Join Date
    02-27-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Convert cell value to new currency using VBA

    Thanks. Please find attached.

    In the example, all the sales figures have been converted to USD.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Convert cell value to new currency using VBA

    Why are you wanting a macro...??? why not just use formulas in the bottom section of code? is there going to be more in the future?

  5. #5
    Registered User
    Join Date
    02-27-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Convert cell value to new currency using VBA

    In total there are 26 columns and the number of rows will vary between 20 and 500. At the moment the data is being imported using VBA, so I'd like to see if I can calculate/replace the values using the same macro.

  6. #6
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Convert cell value to new currency using VBA

    Currency example 1 - Copy.xlsm

    This is a ghetto way to do it but if you want it done tonight that is all I am getting you... you should be able to work with the code to achieve your desired results...

  7. #7
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Convert cell value to new currency using VBA

    I just seen your latest post... knowing that you will need it to detect the lastrow instead of setting it in the code like I did and it will be much easier to work with.. yes it is possible and shouldnt be very hard. you wont need to set the currency on page though you can set it in the background for each one and do all of the calculations using vba and make things much simpler... mine is extremely basic I didnt realize you were working with so much data

  8. #8
    Registered User
    Join Date
    02-27-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Convert cell value to new currency using VBA

    Thanks for your help so far.
    I should also point out that I want to replace the value in the same cell. In this example I've included the output section (below row 17) only to demonstrate the result. Ideally I need the value to be converted in its original cell.

  9. #9
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Convert cell value to new currency using VBA

    Ok makes much more sense mow. I am away from the computer right now but will get back tomorrow

  10. #10
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Convert cell value to new currency using VBA

    Currency Example.xlsm


    Try that out on the bigger worksheet its a dirty way of doing it but it should work...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Forumla to convert currency, but doesn't lose formula in cell
    By smallbizguy10 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-24-2014, 07:17 AM
  2. Replies: 5
    Last Post: 09-16-2014, 01:24 PM
  3. convert currency to text
    By Excel_Instructor in forum Excel General
    Replies: 8
    Last Post: 06-15-2006, 01:25 PM
  4. Cell Date / Currency Convert
    By sparx in forum Excel General
    Replies: 2
    Last Post: 11-28-2005, 11:32 AM
  5. Convert currency to $X.XMM
    By StephanieH in forum Excel General
    Replies: 3
    Last Post: 06-13-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1