学习日志
全民一起VBA提高篇
第九回 万千数据划归同组,排列有序统一命名
相关知识点
1、dim a(9) as long 这样定义的数组有10个元素 从 a(0)开始到a(9)
dim a(3 to 6) 从a(3) 到 a(6)
对于数组中每个元素循环时 可用
for i = lbound(a) to upbound(a)
next i
公元2019年转换大写汉字,无单位
Option Explicit
Sub testyear()
Dim s$, a(9) As String, i%, j%, sc As String
s = CStr(Trim(Cells(7, 2)))
a(0) = "零": a(1) = "壹": a(2) = "贰": a(3) = "叁": a(4) = "肆": a(5) = "伍" & _
a(6) = "陆": a(7) = "柒": a(8) = "捌": a(9) = "玖"
j = Len(s)
sc = ""
For i = 1 To j
If IsNumeric(Mid(s, i, 1)) Then
sc = sc + a(Mid(s, i, 1))
Else
sc = sc + Mid(s, i, 1)
End If
Next i
Cells(7, 3) = sc
End Sub
公元2019年转换大写汉字,带单位
option explicit
Sub testyear2()
Dim s$, a(9) As String, i%, j%, sc As String, b(3), k, m
'将单元格数据去除两端空格,并转换为字符串,存于变量 s 中
s = CStr(Trim(Cells(7, 2)))
'定义与阿拉伯数字对应的大写数字的数组
a(0) = "零": a(1) = "壹": a(2) = "贰": a(3) = "叁": a(4) = "肆": a(5) = "伍" & _
a(6) = "陆": a(7) = "柒": a(8) = "捌": a(9) = "玖"
'将字符串s的长度赋给变量 j
j = Len(s)
'将输出字符串清空
sc = ""
'定义单位数组
b(0) = "仟": b(1) = "佰": b(2) = "拾": b(3) = ""
'for循环用于判断给定字符串中代表年份的数字有几位,存在k中
k = 0
For i = 1 To j
If IsNumeric(Mid(s, i, 1)) Then
k = k + 1
End If
Next i
'循环扫描字符串中每一个字符,若为数字,则将其换为大写,并根据其数字位数匹配
'对应的单位;若不是数字,则照抄
m = 0
For i = 1 To j
If IsNumeric(Mid(s, i, 1)) Then
If k = 4 Then
sc = sc + a(Mid(s, i, 1)) & b(m)
m = m + 1
ElseIf k = 3 Then
sc = sc + a(Mid(s, i, 1)) & b(m + 1)
m = m + 1
ElseIf k = 2 Then
sc = sc + a(Mid(s, i, 1)) & b(m + 2)
m = m + 1
Else
sc = sc + a(Mid(s, i, 1))
End If
Else
sc = sc + Mid(s, i, 1)
End If
Next i
Cells(7, 4) = sc
End Sub