|
- Sub test()
- Dim d, i%, j%, k%, arr1, arr2
- Set d = CreateObject("scripting.dictionary")
- Sheets("销售明细").Activate
- For i = 2 To [a65535].End(3).Row
- d(Cells(i, 2).Value) = d(Cells(i, 2).Value) & i & ","
- Next
- arr1 = d.keys
- Sheets("查询表").Activate
- Range("a9:n" & Sheets("查询表").UsedRange.Rows.Count + 1).ClearContents
- k = 9
- For i = 0 To UBound(arr1)
- If arr1(i) = [c4].Value Then
- d(arr1(i)) = Left(d(arr1(i)), Len(d(arr1(i))) - 1)
- arr2 = Split(d(arr1(i)), ",")
- For j = 0 To UBound(arr2)
- With Sheets("销售明细")
- If .Cells(arr2(j), 1) >= [k4] And _
- .Cells(arr2(j), 1) <= [n4] Then
- Cells(k, 1) = k - 8: Cells(k, 2) = .Cells(arr2(j), 1)
- Cells(k, 3) = .Cells(arr2(j), 4): Cells(k, 4) = .Cells(arr2(j), 5)
- Cells(k, 5) = .Cells(arr2(j), 6): Cells(k, 6) = .Cells(arr2(j), 10)
- Cells(k, 7) = .Cells(arr2(j), 7): Cells(k, 8) = .Cells(arr2(j), 10)
- Cells(k, 9) = .Cells(arr2(j), 9)
- End If
- End With
- k = k + 1
- Next
- End If
- Next
- d.RemoveAll
- Sheets("回款明细").Activate
- For i = 2 To [a65535].End(3).Row
- d(Cells(i, 3).Value) = d(Cells(i, 3).Value) & i & ","
- Next
- arr1 = d.keys
- Sheets("查询表").Activate
- k = 9
- For i = 0 To UBound(arr1)
- If arr1(i) = [c4].Value Then
- d(arr1(i)) = Left(d(arr1(i)), Len(d(arr1(i))) - 1)
- arr2 = Split(d(arr1(i)), ",")
- For j = 0 To UBound(arr2)
- With Sheets("回款明细")
- If .Cells(arr2(j), 1) >= [k4] And _
- .Cells(arr2(j), 1) <= [n4] Then
- Cells(k, 10) = .Cells(arr2(j), 1): Cells(k, 11) = .Cells(arr2(j), 7)
- Cells(k, 12) = .Cells(arr2(j), 8): Cells(k, 13) = .Cells(arr2(j), 9)
- End If
- End With
- k = k + 1
- Next
- End If
- Next
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|