vba Excel宏将单元格格式更改为货币
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13382091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Macro to Change Cell Format to Currency
提问by Irina
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.
我有一个文件，其中一列是数字，第二列是每个数字的相应货币。我有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!
谢谢！
回答by John Bustos
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:
我不确定我是否理解您的确切问题，但这将采用 A 列中的所有值，并根据 B 列中的货币值将它们格式化为 C 列中的货币：
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.
如果要更改货币类型，请录制一个宏，在其中将单元格设置为正确的货币，然后根据需要复制并粘贴该数字格式。
