ExcelBanter - Write a macro to find smt.

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