Excel Macro to Change Cell Format to Currency

Question

I have a file where a column is full of numbers, and the second column has the respective currency of each number. I have USD, EUR, CHF, and GBP.

I want to create a macro that will format the number column from number to currency, and then apply the currency from the second column.

The trick to this is that I want the number column to still have numbers in it, as in the number can be added or subtracted. Thus, I don't want a simple join of the two columns because that would make the fields text, and unable to be used in mathematical equations.

Please help if you know how.

Thank you!

Answer 1

I'm not sure I understand your exact issue, but this will take all the values in Column A and format them as currency in Column C based upon the currency value in Column B:

Sub Macro1()

Dim cl As Range

For Each cl In Intersect(ActiveSheet.Range("A:A"), ActiveSheet.UsedRange)
   cl.Offset(0, 2).Value = cl.Value
   Select Case cl.Offset(0, 1).Value
      Case "USD"
         cl.Offset(0, 2).NumberFormat = "$#,##0.00"
      Case "EUR"
         cl.Offset(0, 2).NumberFormat = "[$€-2] #,##0.00"
      Case "GBP"
         cl.Offset(0, 2).NumberFormat = "[$£-809]#,##0.00"
      Case "CHF"
         cl.Offset(0, 2).NumberFormat = "[$CHF] #,##0.00"
   End Select

Next cl

End Sub

If you want to change the currency types, record a macro where you set a cell to the correct currency then copy and paste that number format as needed.