Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgUSD As Range, rgCurrency As Range, rgForeign As Range
Set rgUSD = Range("A2")
Set rgForeign = Range("C2")
Set rgCurrency = Range("D2")
If Target.Cells.Count = 1 And Not Intersect(Union(rgUSD, rgForeign), Target) Is Nothing Then
Application.EnableEvents = False
If Not Intersect(rgUSD, Target) Is Nothing Then
If IsNumeric(rgUSD.Value) And rgUSD.Value <> 0 Then
rgForeign.Formula = _
"=IF(" & rgCurrency.Address & "="""",""""," & rgUSD.Address & "*VLOOKUP(" & rgCurrency.Address & ",ExchangeRates,2,FALSE))"
Else
rgForeign.ClearContents
End If
ElseIf Not Intersect(rgForeign, Target) Is Nothing Then
If rgForeign.HasFormula = False And IsNumeric(rgForeign.Value) And rgForeign.Value <> 0 Then
rgUSD.Formula = _
"=IF(" & rgCurrency.Address & "="""",""""," & rgForeign.Address & "/VLOOKUP(" & rgCurrency.Address & ",ExchangeRates,2,FALSE))"
Else
rgUSD.ClearContents
End If
End If
Application.EnableEvents = True
End If
End Sub
CurrencyConversionQ28342186.xlsm
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgUSD As Range, rgCurrency As Range, rgForeign As Range
Set rgUSD = Range("A2:A10") 'An amount in US dollars
Set rgForeign = Range("C2:C10") 'An amount in a foreign currency
Set rgCurrency = Range("D2:D10") 'Name of the foreign currency
If Target.Cells.Count = 1 And Not Intersect(Union(rgUSD, rgForeign), Target) Is Nothing Then
Set rgUSD = Intersect(Target.EntireRow, rgUSD)
Set rgForeign = Intersect(Target.EntireRow, rgForeign)
Set rgCurrency = Intersect(Target.EntireRow, rgCurrency)
Application.EnableEvents = False
If Not Intersect(rgUSD, Target) Is Nothing Then
If IsNumeric(rgUSD.Value) And rgUSD.Value <> 0 Then
rgForeign.Formula = _
"=IF(" & rgCurrency.Address & "="""",""""," & rgUSD.Address & "*VLOOKUP(" & rgCurrency.Address & ",ExchangeRates,2,FALSE))"
Else
rgForeign.ClearContents
End If
ElseIf Not Intersect(rgForeign, Target) Is Nothing Then
If rgForeign.HasFormula = False And IsNumeric(rgForeign.Value) And rgForeign.Value <> 0 Then
rgUSD.Formula = _
"=IF(" & rgCurrency.Address & "="""",""""," & rgForeign.Address & "/VLOOKUP(" & rgCurrency.Address & ",ExchangeRates,2,FALSE))"
Else
rgUSD.ClearContents
End If
End If
Application.EnableEvents = True
End If
End Sub
CurrencyConversionQ28342186.xlsm
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Excel compare strings | 6 | 55 | |
Excel not storing numeric string correctly | 5 | 43 | |
Cell Manipulation | 37 | 54 | |
need a macro to find the 2 letter word in a cell | 4 | 13 |
Join the community of 500,000 technology professionals and ask your questions.