+ Reply to Thread
Results 1 to 4 of 4

Dollars/Euros - handle multiple price fields

  1. #1
    Guest

    Dollars/Euros - handle multiple price fields

    I have a tricky situation whereby I need to be able to allow two price
    fields for the user to enter and the user will not always put in both
    values:

    Field 1
    - US Dollars

    Field 2
    - Euro


    Somehow need to show what the price will be in for either currency without
    making too many calculation columns....

    Is there a way to do this?

    Thanks
    Jason



  2. #2
    Registered User
    Join Date
    12-29-2004
    Location
    Wisconsin
    Posts
    8
    Jason -


    Need a little more info.

    Lets Says:
    Column A1 is $
    Column B1 is Euro

    What if both are filled in? What do you want to see and where?

    You could use concatenate function.

    In C1 try =CONCATENATE("$"&A1&" "&"/"&" ","?"&B1)

    I put the ? in the formula because I am not sure what the Euro symbol is.

    Is this what your looking for?

    Kadens Dad

  3. #3
    Registered User
    Join Date
    12-29-2004
    Location
    Wisconsin
    Posts
    8
    Better yet -

    Try

    In C1

    =IF(AND(A1>0,B1>0),CONCATENATE("$"&A1&" "&"/"&" ","?"&B1),IF(A1>0,"$" &A1,IF(B1>0,"?" &B1," ")))


    Kadens Dad

  4. #4
    Peter T
    Guest

    Re: Dollars/Euros - handle multiple price fields

    Hi Jason,

    Maybe a worksheet change event would suit your requirements. Right click the
    sheet tab, View code, and insert following (adapt ranges to suit):

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, cell As Range
    Dim rDollars As Range, rEuros As Range
    Dim xRate As Single

    xRate = 1.35 ' better to get from some named cell

    Set rDollars = Range("B2:B20") ' defined ranges maybe
    Set rEuros = Range("C2:C20")

    On Error GoTo errH
    Application.EnableEvents = False

    Set rng = Intersect(rDollars, Target)
    If Not rng Is Nothing Then
    For Each cell In rng
    Cells(cell.Row, rEuros.Columns(1).Column) = cell / xRate
    Next
    Else
    Set rng = Intersect(rEuros, Target)
    If Not rng Is Nothing Then
    For Each cell In rng
    Cells(cell.Row, rDollars.Columns(1).Column) = cell * xRate
    Next
    End If
    End If

    Application.EnableEvents = True
    Exit Sub
    errH:
    Resume Next
    End Sub

    If you define ranges for dollar & Euro prices, each in single columns with
    same number of rows, modify the code like this:

    Set rng = Intersect(Range("Dollars"), Target)
    and similarly for Euros

    Probably the code should include a more complete error handler in case user
    inserts some text.

    Regards,
    Peter

    <jason@catamaranco.com> wrote in message
    news:u3XXoWc8EHA.2032@tk2msftngp13.phx.gbl...
    > I have a tricky situation whereby I need to be able to allow two price
    > fields for the user to enter and the user will not always put in both
    > values:
    >
    > Field 1
    > - US Dollars
    >
    > Field 2
    > - Euro
    >
    >
    > Somehow need to show what the price will be in for either currency without
    > making too many calculation columns....
    >
    > Is there a way to do this?
    >
    > Thanks
    > Jason
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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