本帖最后由 suye1010 于 2013-1-7 13:22 编辑
- Sub Outbound()
- Dim arr0, arr1, arr2, arr3, i As Integer
- arr0 = Sheets("进销记录").UsedRange
- On Error Resume Next
- arr1 = Application.Transpose(Array("商品编码", "商品名称", "日期", "出库"))
- arr2 = Application.Transpose(Array("商品编码", "商品名称", "日期", "出库"))
- arr3 = Application.Transpose(Array("商品编码", "商品名称", "日期", "出库"))
- For i = 2 To UBound(arr0)
- If arr0(i, 3) = "出库" And arr0(i, 1) <> "" And arr0(i, 14) <> "" And Month(DateValue(arr0(i, 1))) = Val(Sheets("仅出库").Cells(1, 2)) Then
- Select Case arr0(i, 4)
- Case Sheets("仅出库").Cells(2, 2)
- ReDim Preserve arr1(1 To 4, 1 To UBound(arr1, 2) + 1)
- arr1(1, UBound(arr1, 2)) = arr0(i, 14)
- arr1(2, UBound(arr1, 2)) = arr0(i, 15)
- arr1(3, UBound(arr1, 2)) = Day(DateValue(arr0(i, 1)))
- arr1(4, UBound(arr1, 2)) = arr0(i, 11)
- Case Sheets("仅出库").Cells(2, 6)
- ReDim Preserve arr2(1 To 4, 1 To UBound(arr2, 2) + 1)
- arr2(1, UBound(arr2, 2)) = arr0(i, 14)
- arr2(2, UBound(arr2, 2)) = arr0(i, 15)
- arr2(3, UBound(arr2, 2)) = Day(DateValue(arr0(i, 1)))
- arr2(4, UBound(arr2, 2)) = arr0(i, 11)
- Case Sheets("仅出库").Cells(2, 10)
- ReDim Preserve arr3(1 To 4, 1 To UBound(arr3, 2) + 1)
- arr3(1, UBound(arr3, 2)) = arr0(i, 14)
- arr3(2, UBound(arr3, 2)) = arr0(i, 15)
- arr3(3, UBound(arr3, 2)) = Day(DateValue(arr0(i, 1)))
- arr3(4, UBound(arr3, 2)) = arr0(i, 11)
- End Select
- End If
- Next i
- Sheets("仅出库").Cells(3, 1).Resize(UBound(arr1, 2), UBound(arr1)) = Application.Transpose(arr1)
- Sheets("仅出库").Cells(3, 5).Resize(UBound(arr2, 2), UBound(arr2)) = Application.Transpose(arr2)
- Sheets("仅出库").Cells(3, 9).Resize(UBound(arr3, 2), UBound(arr3)) = Application.Transpose(arr3)
- End Sub
复制代码 |