excel - 在价格中添加货币符号

Question

在 Excel 中，我有一系列R1:W300包含价格、日期、百分比、文本等的单元格。

我只想在所有价格之后加上货币符号。

我使用下面的代码，但它将货币符号添加到所有数据中——不仅仅是价格。我怎样才能让它只在价格上加上货币符号？

Sub FormatCurrency()    
    Dim Sh As Worksheet   
    Set Sh = ThisWorkbook.Sheets(1)    

    Sh.Range("R1:W300").NumberFormat = "#,##0.00 $"
End Sub

标签： excelvba

Answer 1

一个简单的循环和如下If语句应该可以：

Option Explicit

Public Sub FormatCurrency()    
    Dim Sh As Worksheet   
    Set Sh = ThisWorkbook.Sheets(1)    

    Dim Cell As Range
    For Each Cell In Sh.Range("R1:W300").Cells 'loop through all cells in that range
        If Cell.NumberFormat = "General" Then  'check if numberformat is General
            Cell.NumberFormat = "#,##0.00 $"   'if general change it to currency
        End If
    Next Cell
End Sub