本帖最后由 Excel学徒123 于 2016-9-13 18:43 编辑
- Option Explicit
- Function ChangeCode(rng As Range) As String
- Dim strText$, StrLeft$, strRight$, strRst$, strVal$
- Dim i%, j%
- Dim blComma As Boolean
- Dim arrName, arrCode
- arrName = Array("尺", "寸", "分")
- arrCode = Array("", "十", "百", "千", "万", "十万", "百万", "千万", "亿")
- blComma = (InStr(rng.Value, ".") > 0)
- If blComma Then
- StrLeft = Left(rng.Value, InStr(rng, ".") - 1)
- strRight = Replace(rng.Value, StrLeft & ".", "")
- For i = 1 To Len(strRight)
- If Mid(strRight, i, 1) * 1 <> 0 Then
- strText = strText & Application.WorksheetFunction.Text(Mid(strRight, i, 1), "[DBNum2]0") & arrName(i - 1)
- End If
- Next
- If Len(StrLeft) = 0 Then
- strRst = "为鲁班尺 " & strText
- Else
- If Len(StrLeft) > 1 Then
- For j = 1 To Len(StrLeft)
- strVal = strVal & Application.WorksheetFunction.Text(Mid(StrLeft, j, 1), "[DBNUM2]0") & arrCode(Len(StrLeft) - j)
- Next
- Else
- strVal = Application.WorksheetFunction.Text(StrLeft, "[DBNum2]0")
- End If
- strRst = "为鲁班尺 " & strVal & "丈" & strText
- End If
- End If
- ChangeCode = strRst
- End Function
复制代码 勉强写了个自定义函数,没写完,忙着下班,自己参考下吧 |