|
测试代码,结果中有很多是负数呢??
- Private Sub Worksheet_Activate()
- On Error Resume Next
- Sheets("总库存").Range("a4:m60000") = ""
- If Sheets("进库").Range("a4") = "" Then
- MsgBox "进库现在是空的,请先把货品录入到进库以后再看总库存!", vbInformation, "进库为空!"
- Exit Sub
- End If
- Dim 棋盘(1 To 65536, 1 To 12)
- Dim 行数, L As Byte
- Dim 进库, x, k
- Set d = CreateObject("scripting.dictionary")
- 进库 = Sheets("进库").Range("a4:l" & Sheets("进库").Range("a65536").End(xlUp).Row)
- For x = 1 To UBound(进库)
- If d.exists(进库(x, 1)) Then
- 行数 = d(进库(x, 1))
- For L = 3 To 12
- If L = 9 Or L = 11 Then L = L + 1
- 棋盘(行数, L) = 棋盘(行数, L) + 进库(x, L)
- Next
- Else
- k = k + 1
- d(进库(x, 1)) = k
- For L = 1 To 12
- 棋盘(k, L) = 进库(x, L)
- Next
- End If
- Next x
- Sheets("总库存").Range("a4").Resize(k, 12) = 棋盘
- If Sheets("出库").Range("a4") = "" Then
- MsgBox "出库现在是空的,如果想看库存,就直接看进库就可以了!", vbInformation, "出库还没有内容!"
- Exit Sub
- End If
- Dim 出库
- 出库 = Sheets("出库").Range("a4:l" & Sheets("出库").Range("a65536").End(xlUp).Row)
- For x = 1 To UBound(出库)
- If d.exists(出库(x, 1)) Then
- 行数 = d(出库(x, 1))
- For L = 3 To 12
- If L = 9 Or L = 11 Then L = L + 1
- 棋盘(行数, L) = 棋盘(行数, L) - 出库(x, L)
- Next
- Else
- k = k + 1
- d(出库(x, 1)) = k
- For L = 1 To 12
- 棋盘(k, L) = 出库(x, L)
- Next
- End If
- Next x
- Sheets("总库存").Range("a4").Resize(k, 12) = 棋盘
- If Sheets("返货").Range("a4") = "" Then
- Exit Sub
- End If
- Dim 返货
- 返货 = Sheets("返货").Range("a4:m" & Sheets("返货").Range("a65536").End(xlUp).Row)
- For x = 1 To UBound(返货)
- If d.exists(返货(x, 2)) Then
- 行数 = d(返货(x, 2))
- For L = 3 To 12
- If L = 9 Or L = 11 Then L = L + 1
- 棋盘(行数, L) = 棋盘(行数, L) - 返货(x, L + 1)
- Next
- Else
- k = k + 1
- d(返货(x, 2)) = k
- For L = 1 To 12
- 棋盘(k, L) = 返货(x, L + 1)
- Next
- End If
- Next x
- Sheets("总库存").Range("a4").Resize(k, 12) = 棋盘
- If Sheets("总库存").Range("O7").Value <> Sheets("总库存").Range("O8").Value Then
- MsgBox "进库或出库数据不对", vbInformation, "总库存提示"
- End If
- k = 0
- For x = 1 To UBound(进库)
- If 棋盘(d(进库(x, 1)), 8) = 0 Then
- If k = 0 Then k = x + 3
- If 棋盘(d(进库(x + 1, 1)), 8) <> 0 Then
- s = s & "," & k & ":" & x + 3
- k = 0
- End If
- End If
- Next
- s = Mid(s, 2)
- Sheets("进库").Range(s).Delete xlUp
- s = ""
- k = 0
- For x = 1 To UBound(出库)
- If 棋盘(d(出库(x, 1)), 8) = 0 Then
- If k = 0 Then k = x + 3
- If 棋盘(d(出库(x + 1, 1)), 8) <> 0 Then
- s = s & "," & k & ":" & x + 3
- k = 0
- End If
- End If
- Next
- s = Mid(s, 2)
- Sheets("出库").Range(s).Delete xlUp
- s = ""
- k = 0
- For x = 1 To UBound(返货)
- If 棋盘(d(返货(x, 1)), 8) = 0 Then
- If k = 0 Then k = x + 3
- If 棋盘(d(返货(x + 1, 1)), 8) <> 0 Then
- s = s & "," & k & ":" & x + 3
- k = 0
- End If
- End If
- Next
- s = Mid(s, 2)
- Sheets("返货").Range(s).Delete xlUp
- End Sub
复制代码
|
|