|
发表于 2012-1-9 11:50
|
显示全部楼层
本楼为最佳答案
本帖最后由 sunjing-zxl 于 2012-1-9 11:51 编辑
- Sub 录入数据()
- Dim sht As Worksheet
- Dim i As Long, j As Long, k As Long
- Dim arr, arr1
- Set d = CreateObject("Scripting.Dictionary")
- Application.ScreenUpdating = False
- arr = Range("A2:G" & [E65536].End(xlUp).Row)
- For i = 1 To UBound(arr)
- d(arr(i, 2)) = d.Count + 1
- Next i
- For i = 1 To d.Count
- Set sht = Sheets.Add
- sht.Name = i
- Sheets("凭证打印").Cells.Copy
- With Sheets(sht.Name).Range("A1")
- .PasteSpecial Paste:=xlPasteFormats
- .PasteSpecial Operation:=xlNone
- .PasteSpecial SkipBlanks:=False
- .PasteSpecial Transpose:=False
- .Range("A1").Activate
- End With
- arr1 = Sheets(sht.Name).Range("A1:AB13")
- n = 0
- For j = 1 To UBound(arr)
- If arr(j, 2) = sht.Name Then
- n = n + 1
- If n = 1 Then
- arr1(2, 3) = arr(j, 1)
- End If
- arr1(4 + n, 3) = arr(j, 5)
- If arr(j, 6) <> "" Then
- For k = 1 To Len(arr(j, 6) * 100)
- arr1(4 + n, 14 - k) = Mid(arr(j, 6) * 100, Len(arr(j, 6) * 100) + 1 - k, 1)
- Next k
- End If
- If arr(j, 7) <> "" Then
- For k = 1 To Len(arr(j, 7) * 100)
- arr1(4 + n, 27 - k) = Mid(arr(j, 7) * 100, Len(arr(j, 7) * 100) + 1 - k, 1)
- Next k
- End If
- End If
- Next j
- Sheets(sht.Name).Range("A1").Resize(UBound(arr1), UBound(arr1, 2)) = arr1
- Next i
- Application.ScreenUpdating = True
- End Sub
复制代码 附件:
Book1-sunjing.rar
(14.32 KB, 下载次数: 33)
|
|