|
Sub test()
Dim A, B, i, j, s, dat
With Workbooks.Open(GetPath())
A = .Sheets(1).UsedRange '数据源
.Close 0
End With
Range("a6:f65536").ClearContents
B = Range("A5:F" & UBound(A) + 5) '目标工作簿
dat = [f3] '开始日期
s = 1
For i = 10 To UBound(A)
For j = 15 To UBound(A, 2) Step 4
If A(i, j - 1) <> "" And A(i, 3) > dat Then
s = s + 1
B(s, 1) = A(3, 4) '机种
B(s, 2) = A(6, j) '物品代码
B(s, 3) = A(i, 3) '日期
B(s, 4) = A(i, j - 1) '数量
B(s, 5) = A(i, 10) '发票号
End If
Next j
Next i
[A5].Resize(s, 6) = B
End Sub
Function GetPath()
Dim FileDlg As FileDialog
Set FileDlg = Application.FileDialog(msoFileDialogFilePicker)
If FileDlg.Show = -1 Then GetPath = FileDlg.SelectedItems(1) Else End
Set FileDlg = Nothing
End Function
Desktop2.rar
(80.44 KB, 下载次数: 3)
|
|