|
发表于 2013-3-17 16:27
|
显示全部楼层
本楼为最佳答案
mengdie101 发表于 2013-3-17 16:18
好吧老师,再问一个问题,当提示无符合条件的数据之后,能不能把页面数据清空 - Sub 明细账_清空()
- With Sheets("明细账簿")
- ka = .[B65536].End(xlUp).Row
- If ka > 3 Then
- .Range("b4:j" & ka).ClearContents
- End If
- End With
- End Sub
- Sub 明细账_生成()
- Dim arr
- With Worksheets("凭证录入")
- arr = .Range("a1").CurrentRegion
- End With
- Dim Km$
- Km = [j1]
- If Len(Km) = 0 Then MsgBox "请在J1选择要查询的姓名": Exit Sub
- Dim i As Long, YuE As Double
- Dim arrPos1, arrPos2, arr2(), k&
- ReDim arr2(1 To UBound(arr) - 1, 1 To 9)
- arrPos1 = Array(3, 4, 5, 6, 7, 8, 9)
- arrPos2 = Array(1, 2, 3, 4, 5, 6, 7)
- For i = 3 To UBound(arr)
- If arr(i, 2) Like Km Then
- k = k + 1
- For j = LBound(arrPos1) To UBound(arrPos1)
- arr2(k, arrPos2(j)) = arr(i, arrPos1(j))
- Next
- YuE = YuE + Val(arr2(k, 7)) - Val(arr2(k, 6))
- arr2(k, 9) = YuE
- Select Case YuE
- Case Is = 0: arr2(k, 8) = "平"
- Case Is > 0: arr2(k, 8) = "贷"
- Case Is < 0: arr2(k, 8) = "借"
- End Select
-
- End If
- Next
- If k > 0 Then
- Range("b4").Resize(k, 9) = arr2
- Else
- MsgBox "无符合条件的数据"
- Call 明细账_清空
- End If
- End Sub
复制代码 |
|