vba数据分列 数据分类汇总

Sub 分列() Sheets(＂数据＂).Select'激活“数据”工作表 Selection.ClearContents'清除“数据”表中,当前选中区域的内容 Range(＂B3＂).Select'选中B3单元格 ActiveSheet.Paste'粘贴 ActiveSheet.Paste'粘贴 Sheets(＂统计＂).Select'激活“统计”工作表 End Sub你的代码里根本没有分列啊~

将所有数据复制,粘贴到“记事本”然后保存为一个文档 然后在EXCEL菜单[数据]-[导入外部数据]-[导入数据] 然后按提示操作即可,分栏时选择以空格分栏 OK?

'j表示数组的第几列 a(i, j) = Split(tmp, ＂|＂)(j - 1) '把tmp用”|“符号切割成5段,依次保存到数组第i行的5个列中 Next Loop Close #,xcfCsk

运用数组这个概念,用split这个函数去查找中间的分隔符,然后循环依次向后在单元格中赋值.

Sub 分离() For i = 2 To 7 a = Cells(i, 1) '取1组拼音 If Mid(a, 2, 1) = "h" Then '第2个为"h"则执行 b = Mid(a, 1, 2) '取前2个字符 Cells(i, 2) = b '赋值到单元格 a = .

A列按” , “号分裂:Sub test() Columns(＂a:a＂).TextToColumns Destination:=Range(＂A1＂), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True End Sub

Sub 拆分字符串()Dim str As StringDim n As Integerstr = [a1].Valuen = Application.Ceiling(Len(str) / 12, 1)Debug.Print nFor r = 2 To n + 2 Cells(r, 1).Value = Mid(str, (r - 2) * 12 + 1, 12)Next rEnd Sub

这样子试试吧,呵呵…… Sub 分列() For i = 1 To 7000 Step 1000 Sheet1.Activate Sheet1.Range(Cells(i, 1), Cells(i + 1000 - 1, 1)).Select Selection.Copy Sheets(2).Activate Cells(1, (i + 1000) / 1000).Select ActiveSheet.Paste Next i End Sub

private sub commandbutton1_click()dim r1, r2, i as integerdim arr r1 = 1 r2 = 1 do until cells(r1, 1) = ＂＂ arr = split(cells(r1, 2).value, ＂;＂) for i = 0 to ubound(arr) cells(r2, 3) = cells(r1, 1) cells(r2, 4) = arr(i) r2 = r2 + 1 next r1 = r1 + 1 loopend sub祝你成功!

Sub Macro1()' Selection.TextToColumns Destination:=Range(＂B1＂), DataType:=. TrailingMinusNumbers:=TrueEnd Sub勾选其他,输入空格即可分列