Hi,
I have a table like below:
A B
Currency Price
EUR 10,000
USD 5,000

I need to write a macro which shows the correct price after the client keys
in the currency they want the price to be quoted at.

Pls help me.

Thanks

Suppose your data is in Sheet2

In sheet1 A1 enter 'EUR
In sheet1 B1 enter the below formula

=IF(ISERROR(MATCH(A1,Sheet2!A1:A10)),"",INDEX(Shee t2!B1:B10,MATCH(A1,Sheet2!A1:A10,0)))

The same logic can be used in the macro. Right click sheet tab and view code
and paste the below . The currency value data is in Sheet2 ColA and B. Adjust
the range you would enter your currency.I take this as A1:A10 of Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Sheet2")
If WorksheetFunction.CountIf(ws1.Range("A:A"), Target.Value) 0 Then
lngRow = WorksheetFunction.Match(Target.Value, ws1.Range("A:A"), 0)
Me.Range("B" & Target.Row) = _
WorksheetFunction.Index(ws1.Range("B:B"), lngRow)
End If
End If
Application.EnableEvents = True
End Sub



If this post helps click Yes
---------------
Jacob Skaria


"diepvic" wrote:

Hi,
I have a table like below:
A B
Currency Price
EUR 10,000
USD 5,000

I need to write a macro which shows the correct price after the client keys
in the currency they want the price to be quoted at.

Pls help me.

Thanks

Thx so much.
The excel function is quite simple & easy to use.
Thanks thanks thanks