Automatic conversion of the currency

vladimiratanasiu · Aug 1, 2019

Hello!
I have a table to be filled in with the prices of some products from different offers. Some products are acquired in the Euro currency and the others in the RON currency. My question is if is any possibility (macros, formulas) so that when I insert the value expressed in one currency (e.g. 100 / 21.15372 Euro)in a cell the , the conversion to the second currency can be automatically operated in the other cell (472.73 / 100 RON), avoiding the circular reference or the need for additional content. I also mention that the table includes more rows and columns than the example shown below. Thank you!



[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]Product[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]Exchange rate Euro / RON[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]Price/Unit (Euro)[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]Price / unit (RON)
[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]Product 1[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 64"]4.7273
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64, align: right"]100[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64, align: right"]472.73
[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]Product 2[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64, align: right"]21.15372[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64, align: right"]100
[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

jmacleary · Aug 5, 2019

Hello there. I have assumed that you can insert a row at the top to hold the conversion rate. The following code will automatically fill in the 'other' column whenever a value is entered (i.e. if you type into column 3, column 4 gets automatically updated, type into column 4 and column 3 gets updated.). If you have more columns that have paired vales, just add them to the relevant cases. Put this code in the worksheet code area (right click on the sheet tab and selectView Code, then paste this code in).

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Row > 2 Then  ' make sure only 1 cell has been changed and its not a header row
    Select Case Target.Column
    Case 3
    If Target.Column = 3 Then   ' this is a euro column
        Cells(Target.Row, 4).Value = Target.Value * Range("B1").Value
    End If
    Case 4
    If Target.Column = 4 Then   ' this is a RON column
        Cells(Target.Row, 3).Value = Target.Value / Range("B1").Value
    End If
    Case Else
    End Select
End If

End Sub

Automatic conversion of the currency

vladimiratanasiu

Active Member

Excel Facts

jmacleary

Well-known Member

Similar threads

Forum statistics

Share this page

Automatic conversion of the currency

vladimiratanasiu

Active Member

Excel Facts

jmacleary

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock