Write a macro to find smt.

  • Thread starter Thread starter diepvic
  • Start date Start date
D

diepvic

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(Sheet2!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
 
Back
Top