Hello everyone,
I hope you all are doing well. So I have a macro which I will post below designed to change the formatting of a whole workbook as well as convert all currency constants between differetn currencies and it works GREAT ;-) ... However it is extremely inefficient and in a big work book say 20 sheets or so it runs very very slow in fact too slow fro comfort and I admit I am not the best programmer mostly the code I write is extremely inefficient. So hopefully some here can help reduce its run time. First some background this macro is meant to view 2 cells "currentcurrency" and "desiredcurrency" which contain the name of the currency your in and the currency you want. It then finds the constant you need to multiple by via Vlookup and multiples all currency formatted cells by the consatant. Also what you may find confusing is why I repeat operations the point is to say go from dollars to euros then from euros to pounds then from pounds to dollars and have it be the accurate figure as you first started with. Let me know is anyone has any questions.
Thank you,
your friend

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