需求:某一列单元格内容为逗号分隔的整数序列,形如(11,22);解析并给每一个数字加300000,形如(300011,300022);之后回写单元格即可
Sub Bat_Click()
For Each w In Worksheets(1).UsedRange.Columns(6) '遍历第一张表格的第六列填过的数据
Dim Index As Integer '声明一个临时索引,后边获得当前是第几行的时候会用到
Index = 1
For Each v In w.Value '遍历该列的每一个值
If v <> "result" Then '略过第一行标题头
Dim a As Variant
Dim b As Variant
Dim c As String
a = Split(v, ",") '单元格内容逗号拆分
b = UBound(a) 'UBound获得最大索引(以0为起始)
If b > 0 Then
c = ""
For i = 0 To b
If c <> "" Then
c = c + ","
End If
c = c + CStr(300000 + CInt(a(i))) '原字符串内容拆分的内容数字加3000000(强制转换),之后拼接为新的内容,举例:11,22 ===> 300011,300022
Next
Range("F" + CStr(Index)) = c '单元格内容赋值API——Range(超好用 ^^)
Else
Range("F" + CStr(Index)) = (CStr(300000 + CInt(a(0))))
End If
End If
Index = Index + 1
Next
Next
End Sub
参考:
http://club.excelhome.net/forum.php?mod=viewthread&tid=1499041
http://www.360doc.com/content/17/0902/11/30583536_684110308.shtml
https://jingyan.baidu.com/article/7c6fb428c6f49e80642c9096.html
https://www.yiibai.com/vba