本帖最后由 龙送农 于 2013-12-13 16:48 编辑
这是fffox 老师帮我写的代码。为了方便统计,我将原来的C列“凭证号”分成两列“字、号”C列和D列,想要实现的是:用VBA,点击“请你分解”,“银行账”和“现金账”以E列种类并按日期顺序,将A列至K列信息分解到后边的相关名称表。麻烦大师帮我修改或重新写代码
Sub 分解()
Dim d, arr
Dim i%, j As Byte, k As Byte, iRow%, sh As Object
Dim brr(1 To 100, 1 To 9)
Set d = CreateObject("scripting.dictionary")
For k = 4 To 8 'Sheets.Count
Set sh = Sheets(k)
'If sh.Name <> "银行账" And sh.Name <> "现金账" Then
d(sh.Name) = k
With Sheets("银行账")
arr = .Range("a6:i" & .Range("a65536").End(xlUp).Row)
End With
For i = 1 To UBound(arr)
If d(arr(i, 5)) = k Then
iRow = iRow + 1
For j = 1 To 10
brr(iRow, j) = arr(i, j)
Next
End If
Next
With Sheets("现金账")
arr = .Range("a6:i" & .Range("a65536").End(xlUp).Row)
End With
For i = 1 To UBound(arr)
If d(arr(i, 5)) = k Then
iRow = iRow + 1
For j = 1 To 10
brr(iRow, j) = arr(i, j)
Next
End If
Next
sh.Range("a6").Resize(iRow, 10) = brr
Set sh = Nothing
iRow = 0
Erase brr
'End If
Next
End Sub
|