Sub universalconverter()
Dim sFmt As String
Dim mt As String
Dim xt As String
Dim lookr As Range: Set lookr = Range("currencyrange")
Dim ct As Worksheet: Set ct = Sheets("Currency Table")
Dim wk As Worksheet: Set wk = Sheets("Input")
Dim im As Worksheet
Dim oSh As Worksheet
Dim oCell As Range
Application.ScreenUpdating = False ' Turn-off screen updates
If wk.Range("currentcurrency") Like "*US Dollar*" Then
Select Case wk.Range("desiredcurrency").Value2
Case "US Dollar"
sFmt = "[$$-en-US]#,##0.000"
x = "US Dollar"
Case "Euro"
sFmt = "[$€-en-IE]#,##0.0000"
x = "Euro"
Case "British Pound"
sFmt = "[$£-en-GB]#,##0.000"
x = "British Pound"
Case "Chinese Yuan Renminbi"
sFmt = "[$¥-zh-CN]#,##0.000"
x = "Chinese Yuan Renminbi"
Case "Brazilian Real"
sFmt = "[$BRL]#,##0.000"
x = "Brazilian Real"
Case "Australian Dollar"
sFmt = "[$AUD]#,##0.000"
x = "Australian Dollar"
Case "Korean Won"
sFmt = "[$" & ChrW(8361) & "-ko-KR]#,##0.000"
x = "Korean Won"
Case "Japanese Yen"
sFmt = "[$¥-ja-JP]#,##0.000"
x = "Japanese Yen"
Case "Singapore Dollar"
sFmt = "[$SGD]#,##0.000"
x = "Singapore Dollar"
Case "Indian Rupee"
sFmt = "[$" & ChrW(8377) & "-bn-IN]#,##0.000"
x = "Indian Rupee"
Case "Malaysian Ringgit"
sFmt = "[$MYR]#,##0.000"
x = "Malaysian Ringgit"
Case "Israeli Sheqel"
sFmt = "[$ILS]#,##0.000"
x = "Israeli sheqel"
Case "Russian Ruble"
sFmt = "[$RUB]#,##0.000"
x = "Russian Ruble"
End Select
ActiveWorkbook.Styles("Currency").NumberFormat = sFmt
End If
If wk.Range("currentcurrency") Like "*US Dollar*" Then
Select Case Range("desiredcurrency").Value2
Case "US Dollar"
mt = Application.WorksheetFunction.VLookup("US Dollar", ct.Range("currencyrange"), 2, False)
Case "Euro"
mt = Application.WorksheetFunction.VLookup("Euro", ct.Range("currencyrange"), 2, False)
Case "British Pound"
mt = Application.WorksheetFunction.VLookup("British Pound", ct.Range("currencyrange"), 2, False)
Case "Chinese Yuan Renminbi"
mt = Application.WorksheetFunction.VLookup("Chinese Yuan Renminbi", ct.Range("currencyrange"), 2, False)
Case "Brazilian Real"
mt = Application.WorksheetFunction.VLookup("Brazilian Real", ct.Range("currencyrange"), 2, False)
Case "Australian Dollar"
mt = Application.WorksheetFunction.VLookup("Australian Dollar", ct.Range("currencyrange"), 2, False)
Case "Korean Won"
mt = Application.WorksheetFunction.VLookup("Korean Won", ct.Range("currencyrange"), 2, False)
Case "Japanese Yen"
mt = Application.WorksheetFunction.VLookup("Japanese Yen", ct.Range("currencyrange"), 2, False)
Case "Singapore Dollar"
mt = Application.WorksheetFunction.VLookup("Singapore Dollar", ct.Range("currencyrange"), 2, False)
Case "Indian Rupee"
mt = Application.WorksheetFunction.VLookup("Indian Rupee", ct.Range("currencyrange"), 2, False)
Case "Malaysian Ringgit"
mt = Application.WorksheetFunction.VLookup("Malaysian Ringgit", ct.Range("currencyrange"), 2, False)
Case "Israeli Sheqel"
mt = Application.WorksheetFunction.VLookup("Israeli Sheqel", ct.Range("currencyrange"), 2, False)
Case "Russian Ruble"
mt = Application.WorksheetFunction.VLookup("Russian Ruble", ct.Range("currencyrange"), 2, False)
End Select
For Each oSh In ThisWorkbook.Worksheets
For Each oCell In oSh.UsedRange.Cells
If oCell.Style Like "*Currency*" Then
Application.Goto oCell
oCell.Value = oCell.Value * mt
End If
Next
Next
End If
If wk.Range("currentcurrency") Like "*US Dollar*" Then
wk.Range("currentcurrency") = x
GoTo 3
End If
Select Case wk.Range("desiredcurrency").Value2
Case "US Dollar"
sFmt = "[$$-en-US]#,##0.000"
x = "US Dollar"
Case "Euro"
sFmt = "[$€-en-IE]#,##0.0000"
x = "Euro"
Case "British Pound"
sFmt = "[$£-en-GB]#,##0.000"
x = "British Pound"
Case "Chinese Yuan Renminbi"
sFmt = "[$¥-zh-CN]#,##0.000"
x = "Chinese Yuan Renminbi"
Case "Brazilian Real"
sFmt = "[$BRL]#,##0.000"
x = "Brazilian Real"
Case "Australian Dollar"
sFmt = "[$AUD]#,##0.000"
x = "Australian Dollar"
Case "Korean Won"
sFmt = "[$" & ChrW(8361) & "-ko-KR]#,##0.000"
x = "Korean Won"
Case "Japanese Yen"
sFmt = "[$¥-ja-JP]#,##0.000"
x = "Japanese Yen"
Case "Singapore Dollar"
sFmt = "[$SGD]#,##0.000"
x = "Singapore Dollar"
Case "Indian Rupee"
sFmt = "[$" & ChrW(8377) & "-bn-IN]#,##0.000"
x = "Indian Rupee"
Case "Malaysian Ringgit"
sFmt = "[$MYR]#,##0.000"
x = "Malaysian Ringgit"
Case "Israeli Sheqel"
sFmt = "[$ILS]#,##0.000"
x = "Israeli sheqel"
Case "Russian Ruble"
sFmt = "[$RUB]#,##0.000"
x = "Russian Ruble"
End Select
ActiveWorkbook.Styles("Currency").NumberFormat = sFmt
Select Case Range("Currentcurrency").Value2
Case "US Dollar"
mt = Application.WorksheetFunction.VLookup("US Dollar", ct.Range("currencyrange"), 3, False)
Case "Euro"
mt = Application.WorksheetFunction.VLookup("Euro", ct.Range("currencyrange"), 3, False)
Case "British Pound"
mt = Application.WorksheetFunction.VLookup("British Pound", ct.Range("currencyrange"), 3, False)
Case "Chinese Yuan Renminbi"
mt = Application.WorksheetFunction.VLookup("Chinese Yuan Renminbi", ct.Range("currencyrange"), 3, False)
Case "Brazilian Real"
mt = Application.WorksheetFunction.VLookup("Brazilian Real", ct.Range("currencyrange"), 3, False)
Case "Australian Dollar"
mt = Application.WorksheetFunction.VLookup("Australian Dollar", ct.Range("currencyrange"), 3, False)
Case "Korean Won"
mt = Application.WorksheetFunction.VLookup("Korean Won", ct.Range("currencyrange"), 3, False)
Case "Japanese Yen"
mt = Application.WorksheetFunction.VLookup("Japanese Yen", ct.Range("currencyrange"), 3, False)
Case "Singapore Dollar"
mt = Application.WorksheetFunction.VLookup("Singapore Dollar", ct.Range("currencyrange"), 3, False)
Case "Indian Rupee"
mt = Application.WorksheetFunction.VLookup("Indian Rupee", ct.Range("currencyrange"), 3, False)
Case "Malaysian Ringgit"
mt = Application.WorksheetFunction.VLookup("Malaysian Ringgit", ct.Range("currencyrange"), 3, False)
Case "Israeli Sheqel"
mt = Application.WorksheetFunction.VLookup("Israeli Sheqel", ct.Range("currencyrange"), 3, False)
Case "Russian Ruble"
mt = Application.WorksheetFunction.VLookup("Russian Ruble", ct.Range("currencyrange"), 3, False)
End Select
For Each oSh In ThisWorkbook.Worksheets
For Each oCell In oSh.UsedRange.Cells
If oCell.Style Like "*Currency*" Then
Application.Goto oCell
oCell.Value = oCell.Value * mt
End If
Next
Next
Select Case Range("desiredcurrency").Value2
Case "US Dollar"
mt = Application.WorksheetFunction.VLookup("US Dollar", ct.Range("currencyrange"), 2, False)
Case "Euro"
mt = Application.WorksheetFunction.VLookup("Euro", ct.Range("currencyrange"), 2, False)
Case "British Pound"
mt = Application.WorksheetFunction.VLookup("British Pound", ct.Range("currencyrange"), 2, False)
Case "Chinese Yuan Renminbi"
mt = Application.WorksheetFunction.VLookup("Chinese Yuan Renminbi", ct.Range("currencyrange"), 2, False)
Case "Brazilian Real"
mt = Application.WorksheetFunction.VLookup("Brazilian Real", ct.Range("currencyrange"), 2, False)
Case "Australian Dollar"
mt = Application.WorksheetFunction.VLookup("Australian Dollar", ct.Range("currencyrange"), 2, False)
Case "Korean Won"
mt = Application.WorksheetFunction.VLookup("Korean Won", ct.Range("currencyrange"), 2, False)
Case "Japanese Yen"
mt = Application.WorksheetFunction.VLookup("Japanese Yen", ct.Range("currencyrange"), 2, False)
Case "Singapore Dollar"
mt = Application.WorksheetFunction.VLookup("Singapore Dollar", ct.Range("currencyrange"), 2, False)
Case "Indian Rupee"
mt = Application.WorksheetFunction.VLookup("Indian Rupee", ct.Range("currencyrange"), 2, False)
Case "Malaysian Ringgit"
mt = Application.WorksheetFunction.VLookup("Malaysian Ringgit", ct.Range("currencyrange"), 2, False)
Case "Russian Ruble"
mt = Application.WorksheetFunction.VLookup("Russian Ruble", ct.Range("currencyrange"), 2, False)
Case "Israeli Sheqel"
mt = Application.WorksheetFunction.VLookup("Israeli Sheqel", ct.Range("currencyrange"), 2, False)
End Select
For Each oSh In ThisWorkbook.Worksheets
For Each oCell In oSh.UsedRange.Cells
If oCell.Style Like "*Currency*" Then
Application.Goto oCell
oCell.Value = oCell.Value * mt
wk.Range("currentcurrency") = x
End If
Next
Next
Application.ScreenUpdating = True ' Turn-off screen updates
3 End Sub
Bookmarks