|
发表于 2012-6-25 15:48
|
显示全部楼层
本楼为最佳答案
- Sub 明细账() '按照你现在的表结构编的,如果变动,有可能会出错
- Dim i As Integer, j As Integer, k As Integer, x As Integer
- Dim s1 As String, s2 As String
- Dim x1, x2, x3, x4, x5, x6, x7
- For i = 2 To Worksheets.Count '从第二张工作表开始循环
- s1 = Split(Sheets(i).Name)(0) '取得工作表名中的总账科目
- s2 = Split(Sheets(i).Name)(1) '取得工作表名中的明细科目
- With Sheets("记账凭证")
- For j = 5 To .Range("b65536").End(xlUp).Row '记账凭证的B列第五行开始往下循环
- If .Cells(j, 2) = s1 And .Cells(j, 3) = s2 Then '如果总账科目和明细科目和工作表名中的相等
- x = .Cells(j, 6).End(xlUp).Row '取得凭证号所在的行
- x1 = Split(.Cells(x, 2), " ")(1) '取得记账月
- x2 = Split(.Cells(x, 2), " ")(2) '取得记账日
- x3 = .Cells(x, 6) '取得凭证号
- x4 = IIf(.Cells(j, 1) = "", .Cells(j, 1).End(xlUp).Value, .Cells(j, 1)) '取得摘要
- x5 = .Cells(j, 4) '取得借方
- x6 = .Cells(j, 5) '取得贷方
- If .Cells(j, 4) = "" Then '取得借或贷
- x7 = "贷"
- Else
- x7 = "借"
- End If
- With Sheets(i) '填充明细账
- k = .Range("a20").End(xlUp).Row + 1
- .Cells(k, 1) = x1
- .Cells(k, 2) = x2
- .Cells(k, 4) = x3
- .Cells(k, 5) = x4
- .Cells(k, 6) = x5
- .Cells(k, 7) = x6
- .Cells(k, 8) = x7
- End With
- End If
- Next
- End With
- Next
- End Sub
复制代码 |
|