|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 yjh_27 于 2012-5-8 21:44 编辑
- Sub aa()
- r = Range("a65536").End(3).Row '某物料末行
- r0 = Range("A" & r).End(3).Row '某物料首行
-
-
- Do
- s0 = 0 '上月盘存合计
- s1 = 0 '本月入库合计
- s2 = 0 '本月盘存
- '/计算s0 s1 s2
- For i = r0 To r
- If "盘" Like "[" & Cells(i, 4) & "]" Then
- If Cells(i, 3) = "" Then
- s2 = Cells(i, 5) '本月盘存
- Exit For
- Else
- s0 = s0 + Cells(i, 5)
- End If
- Else
- s1 = s1 + Cells(i, 5)
- End If
- Next
-
- s3 = s0 + s1 - s2 '本月消耗
- r1 = r - i '已有分解行数
- '开始分解
- For i1 = r0 To i - 1
- If Cells(i1, 5) >= s3 Then '有剩余
- s5 = Cells(i1, 5) - s3
- s3 = 0
- If r1 <= 0 Then '已有分解行用完,插入新行
- Rows(r - r1 + 1).Insert Shift:=xlDown
- End If
- '写入数据
- Cells(r - r1 + 1, 1) = Cells(i1, 1)
- Cells(r - r1 + 1, 2) = Cells(i1, 2)
- Cells(r - r1 + 1, 3) = Cells(i1, 3)
- Cells(r - r1 + 1, 4) = Cells(i, 4)
- Cells(r - r1 + 1, 5) = s5
- Cells(r - r1 + 1, 6) = Cells(i1, 3) * s5
- r1 = r1 - 1
-
- Else
- s3 = s3 - Cells(i1, 5) '用完,减消耗
- End If
- Next
-
- If r1 > 0 Then '已有分解行未用完,删除
- Rows(r - r1 + 1).Resize(r1).Delete Shift:=xlUp
- End If
-
- '/下一物料首末行
- r = Range("A" & r0).End(3).Row
- r0 = Range("A" & r).End(3).Row
- Loop While r > 1 '循环至1行
- End Sub
复制代码
|
|