|
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Sub 综合计划与消耗出入统计()
Set sht1 = Worksheets("综合计划")
Set sht2 = Worksheets("综合消耗")
a = sht1.Range("A1").CurrentRegion.Rows.Count
b = sht2.Range("A1").CurrentRegion.Rows.Count
For m = 4 To a
For n = 3 To b
If sht1.Cells(m, 2) = sht2.Cells(n, 2) And sht1.Cells(m, 3) = sht2.Cells(n, 3) And sht1.Cells(m, 5) = sht2.Cells(n, 5) Then
sht2.Cells(n, 10) = "按计划领用"
ElseIf sht1.Cells(m, 2) = sht2.Cells(n, 2) And sht1.Cells(m, 3) = sht2.Cells(n, 3) And sht1.Cells(m, 5) > sht2.Cells(n, 5) Then
sht2.Cells(n, 10) = "按计划领用后剩余" & sht1.Cells(m, 5) - sht2.Cells(n, 5) & sht2.Cells(n, 4)
ElseIf sht1.Cells(m, 2) = sht2.Cells(n, 2) And sht1.Cells(m, 3) = sht2.Cells(n, 3) And sht1.Cells(m, 5) < sht2.Cells(n, 5) Then
sht2.Cells(n, 10) = "超预算领用" & sht2.Cells(n, 5) - sht1.Cells(m, 5) & sht2.Cells(n, 4)
ElseIf sht1.Cells(m, 2) = sht2.Cells(n, 2) And sht1.Cells(m, 3) <> sht2.Cells(n, 3) Then
sht2.Cells(n, 10) = "本月未计划而超预算消耗"
ElseIf sht2.Cells(n, 2) <> sht1.Cells(m, 2) And sht1.Cells(m, 2) <> "" And sht2.Cells(n, 2) <> "" Then
sht1.Cells(m, 8) = "本月未领用"
sht2.Cells(n, 10) = "未计划而超预算消耗"
End If
Next n
Next m
End Sub
用字典做了一个比较的,重新梳理了一下思路。 - Sub 综合计划与消耗出入统计()
- Set sht1 = Worksheets("综合计划")
- Set sht2 = Worksheets("综合消耗")
- sht1.[h4:h1000] = ""
- sht2.[J3:J1000] = ""
- arr = sht1.[a1].CurrentRegion
- brr = sht2.[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary") '计划表有的品种
- Set d1 = CreateObject("scripting.dictionary") '领用表有的品种
- For i = 4 To UBound(arr) '各品种的计划量
- x = arr(i, 2) & arr(i, 3)
- If Len(x) > 0 Then d(x) = arr(i, 5)
- Next
-
- For i = 3 To UBound(brr)
- x = brr(i, 2) & brr(i, 3)
- y = brr(i, 5) '领用量
- If Len(x) > 0 Then
- d1(x) = "" '领用表有
- If d.exists(x) Then '计划表有,领用表有
- s = d(x) - y
- brr(i, 10) = IIf(s = 0, "按计划领用", IIf(s > 0, "按计划领用后剩余" & s, "超预算领用" & (-s)))
- Else '计划表无,领用表有
- brr(i, 10) = "本月未计划而超预算消耗"
- End If
- End If
- Next
- sht2.[J1].Resize(UBound(brr)) = Application.Index(brr, , 10)
-
- For i = 4 To UBound(arr) '
- x = arr(i, 2) & arr(i, 3)
- If Len(x) > 0 And Not d1.exists(x) Then arr(i, 8) = "本月未领用" '计划表有,领用表无
- Next
- sht1.[H1].Resize(UBound(arr)) = Application.Index(arr, , 8)
- End Sub
复制代码
|
|