VBA写入公式（4）：数字转大写金额公式

写入大写金额公式：=SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,“负”)&TEXT(INT(FIXED(ABS(A2))),“[dbnum2]G/通用格式元;;”)&TEXT(RIGHT(FIXED(A2),2),“[dbnum2]0角0分;;”&IF(ABS(A2)>1%,“整”,)),“零角”,IF(ABS(A2)<1,“零”)),“零分”,“整”)

Sub 金额大写()
    '先选择数字所在的一个单元格，在选择大写金额保存的一个单元格。带有双引号的外面必须由两重双引号，而且最内侧双引号外不能连接其他字符串。
    Set rng1 = Application.InputBox(prompt:="选择数据源的一个单元格", Type:=8)
    'rng1.Select
    a = rng1.Address(0, 0)
'    Debug.Print rng1
'    Debug.Print rng1_addr
'    a = rng1_addr
    Set rng2 = Application.InputBox(prompt:="选择保存结果一个单元格", Type:=8)
'    row2 = rng2.Row
'    colu2 = rng2.Column
    rng2.Formula = "=SUBSTITUTE(SUBSTITUTE(IF(" & a & ">-0.5%,," & """负""" & ")&TEXT(INT(FIXED(ABS(" & a & ")))," & """[dbnum2]G/通用格式元;;""" & ")&TEXT(RIGHT(FIXED(" & a & "),2)," & """[dbnum2]0角0分;;""" & "&IF(ABS(" & a & ")>1%," & """整""" & ",))," & """零角""" & ",IF(ABS(" & a & ")<1,," & """零""" & "))," & """零分""" & "," & """整""" & ")"
'    For Each i In rng1
'        'Cells(row2, colu2).Formula = "=" & a & "+" & a & "+" & a
'        'Cells(row2, colu2).Formula = "=SUBSTITUTE(" & a & "," & "1" & "," & "3" & ")"
'        '=SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")
'        '带有双引号的外面必须由两重双引号，而且最内侧双引号外不能连接其他字符串。
'        rng2.Formula = "=SUBSTITUTE(SUBSTITUTE(IF(" & a & ">-0.5%,," & """负""" & ")&TEXT(INT(FIXED(ABS(" & a & ")))," & """[dbnum2]G/通用格式元;;""" & ")&TEXT(RIGHT(FIXED(" & a & "),2)," & """[dbnum2]0角0分;;""" & "&IF(ABS(" & a & ")>1%," & """整""" & ",))," & """零角""" & ",IF(ABS(" & a & ")<1,," & """零""" & "))," & """零分""" & "," & """整""" & ")"
'        row2 = row2 + 1
'    Next
End Sub

去除备注：

Sub 金额大写()
    Set rng1 = Application.InputBox(prompt:="选择数据源的一个单元格", Type:=8)
    a = rng1.Address(0, 0)
    Set rng2 = Application.InputBox(prompt:="选择保存结果一个单元格", Type:=8)
    rng2.Formula = "=SUBSTITUTE(SUBSTITUTE(IF(" & a & ">-0.5%,," & """负""" & ")&TEXT(INT(FIXED(ABS(" & a & ")))," & """[dbnum2]G/通用格式元;;""" & ")&TEXT(RIGHT(FIXED(" & a & "),2)," & """[dbnum2]0角0分;;""" & "&IF(ABS(" & a & ")>1%," & """整""" & ",))," & """零角""" & ",IF(ABS(" & a & ")<1,," & """零""" & "))," & """零分""" & "," & """整""" & ")"
End Sub