|
本帖最后由 xhzhengzheng 于 2014-11-22 10:11 编辑
以前网友提问过,sheet1一组数据如何自动分类放在sheet2 sheet3中,贴地址为:http://www.excelpx.com/thread-111304-1-1.html。版主兰色幻想回贴,所写VBA代码如下:
Sub 分类存放()
Dim arr, arr1, arr2()
Dim myrow
myrow = Sheets("sheet1").Range("h65536").End(xlUp).Row - 1
arr = Sheets("sheet1").Range("a2:h" & myrow + 1)
Set d = CreateObject("Scripting.Dictionary")
'取得唯一的分类
For x = 1 To myrow
d(arr(x, 8)) = arr(x, 8)
Next x
arr1 = d.keys
For y = 0 To UBound(arr1)
ReDim arr2(1 To myrow, 1 To 8)
For x = 1 To myrow
If arr(x, 8) = arr1(y) Then
k = k + 1
For j = 1 To 8
arr2(k, j) = arr(x, j)
Next j
End If
Next x
Set mysheet = Sheets.Add
mysheet.Name = arr1(y)
mysheet.Range("a1:h1").Value = Sheets("sheet1").Range("a1:h1").Value
mysheet.Range("a2").Resize(k, 8) = arr2
Erase arr2
k = 0
Next y
End Sub
本人对语句不太熟悉,想请教版主,另一张表,见附件。
第一,如要指定第12列(进货总单ID)为条件列,原语句如何修改。
第二,如果要把条件筛选后,自动生成sheet2 ,sheet3,sheet4,。。。。,原语句中的如何修改,因为原语句自动生成的是按列中名称自动命名的。
第三,如果能自动生成sheet2 ,sheet3,sheet4,。。。。,后,如果sheet2 ,sheet3,sheet4,。。。。中的列,只需取sheet1中,某些列,又如何修改。例如只取sheet1中的,第1,第3,4,5,6,7,8,9列字段,上述语句如何修改。
请版本多多指教,这个案例对日常工作非常重要,如果能够做成,那是提高了很大的效率。
本帖最后由 dsmch 于 2014-11-24 10:06 编辑
- Sub Macro1()
- On Error Resume Next
- Dim arr, brr, d, w, i&, j%, k%, k2%, s&
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- w = Array(1, 3, 4, 5, 6, 7, 8, 9)
- ReDim brr(1 To UBound(arr), 1 To UBound(w) + 1)
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- For i = Sheets.Count To 2 Step -1
- Sheets(i).Delete
- Next
- For i = 2 To UBound(arr)
- If Not d.exists(arr(i, 12)) Then d(arr(i, 12)) = i Else d(arr(i, 12)) = d(arr(i, 12)) & "," & i
- Next
- a = d.keys: b = d.items
- For i = 0 To d.Count - 1
- x = Split(b(i), ","): s = 0
- For j = 0 To UBound(x)
- s = s + 1
- For k = 0 To UBound(w)
- brr(s, k + 1) = arr(x(j), w(k))
- Next
- Next
- With Sheets.Add(after:=Sheets(Sheets.Count))
- For k2 = 0 To UBound(w)
- .Cells(1, k2 + 1) = arr(1, w(k2))
- Next
- .Range("a2").Resize(s, UBound(w) + 1) = brr
- .Columns.AutoFit
- .Name = "Sheet" & i + 2
- End With
- Next
- Sheet1.Activate
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|