Formula Set Up

[Frick]

I have 3 col's each for different currencies


a1 = Curr 1 B1 = Curr 2 C1 = Curr3
a2 = rate 1 b2 = rate 2 c2 = rate3


In rows 5 through 15, I input numbers for cost in the currency
reflected rate.

I can write a simple formula that will convert the input cell to the
other exchanges but what I would like toknow is if there is a
condition that basically says for example in cell a6, if a6 is 0 and
b6 >0 then b6 times the exchange, or if a6 is 0 and c6 >0 then c6
times the exchange into a6.

 

[J.E. McGimpsey]

You can't have both a formula and a value in a cell. However, you
can use a Worksheet_Change() macro to do what you want.

I'm assuming that your rates are tied to a common currency, so that
to convert b to a you need to divide b6 by B2, then multiply by A2,
and likewise to convert to c, you need to divide by B2 then
multiply by C2 (otherwise you get inconsistent conversions).

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rateRange As Range
Dim temp As Double
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A5:C15")) Is Nothing Then
Set rateRange = Range("A2:C2")
Application.EnableEvents = False
temp = .Value / rateRange(.Column)
With Cells(.Row, 1).Resize(1, 3)
Select Case Target.Column
Case 1
.Item(2).Value = temp * rateRange(2)
.Item(3).Value = temp * rateRange(3)
Case 2
.Item(1).Value = temp * rateRange(1)
.Item(3).Value = temp * rateRange(3)
Case Else
.Item(1).Value = temp * rateRange(1)
.Item(2).Value = temp * rateRange(2)
End Select
End With
Application.EnableEvents = True
End If
End With
End Sub