New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Interdependent cells : Currency converter

Free Excel Macros

Interdependent cells : Currency converter

Postby Sisyphus » Fri Oct 19, 2012 9:14 pm

Daily practice of worksheet design requires the value of a cell being determined by that of another. So, you enter a value in one cell and a formula in the other. But what if you need to give the user the option of entering either value? The theory - and solution - may be demonstrated by a currency converter. You know the exchange rate and you would like to apply it to whatever amount the user enters in either currency to determine the amount in the other.

The attached workbook realizes this concept. However, the solution offered is by no means limited to such a simple calculation. The code has been designed to keep the two calculations, resulting from the user's choice of which cell to enter his data, distinctly separate from each other. Therefore the system is capable of handling completely different calculations. For example, if applied to a data retrieval task the user might look for an item by either entering its price in one cell or its color in another. The resulting look-ups aren't in any way related by a common exchange rate, and that is the advantage of the attached code which handles the two tasks separately.

The code comes in two parts. Drag or copy/paste the module Convert to your own project. Then paste this code to the worksheet's code module where your two cells are.
Code: Select all
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
   
    With Application
        Set Rng = .Union(Range(Cone), Range(Ctwo))
        If Not .Intersect(Rng, Target) Is Nothing Then
            Convert Target, Rng   ', Conv
        End If
    End With
End Sub

At the top of the code in the module Convert there are two constants defining the two inter-related cells.
Code: Select all
    Public Const Cone As String = "C6"
    Public Const Ctwo As String = "D6"
In the example they are adjecent to each other, but you can change these addresses to be anywhere on one worksheet. Also, in the example, the exchange rate is supplied from a cell in the same worksheet. The code doesn't require any input from the worksheet. When you create the formulas that determine the value of the opposite cell you are entirely free to use data from any source including data stored in the code itself.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 23 guests