Dollars/Euros - handle multiple price fields

[Guest]

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

 

[Peter T]

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