本帖最后由 micch 于 2018-12-27 18:01 编辑
看了楼主的标注,实在惭愧,我这学习态度明显不够认真,学习一下
- Sub test()
- Dim i%, k%, m%, n%
- ar = Sheet2.UsedRange '=========替换内容表存入数组
- arr = Sheet1.[a1].CurrentRegion '====销售明细表原内容存入数组
- ReDim brr(999, 1 To UBound(arr, 2)) '定义数组存放替换结果,999选足够大数字
- For i = 2 To UBound(arr) '========对明细表每一行内容循环
- For k = 1 To UBound(ar) '========明细表每一行内容与替换内容比对
- '================因为不确定数据是否顺序排列所以全部循环比对一次
- If arr(i, 1) = ar(k, 1) And arr(i, 3) = ar(k, 2) Then '===如果日期,品名一致
- For m = 1 To 5 '======brr增加两行数据,一行为原数据,一行为新客户
- brr(n, m) = arr(i, m): brr(n + 1, m) = arr(i, m)
- Next
- brr(n + 1, 2) = "新客户": brr(n + 1, 5) = ar(k, 4) '新客户B,E列赋值
- If ar(k, 3) >= arr(i, 4) Then '=============数量发生变化
- brr(n, 4) = 0
- brr(n + 1, 4) = arr(i, 4)
- ar(k, 3) = ar(k, 3) - arr(i, 4) '=========替换表当前行数量为剩余数量
- Else
- brr(n, 4) = arr(i, 4) - ar(k, 3)
- brr(n + 1, 4) = ar(k, 3)
- ar(k, 2) = "" '==============替换表当前行替换完毕,不再参与比对
- End If
- n = n + 2: GoTo 1
- End If
- Next k
- For m = 1 To 5: brr(n, m) = arr(i, m): Next '比对无替换,原明细直接赋值结果数组
- n = n + 1
- 1
- Next i
- For i = 0 To n: brr(i, 6) = brr(i, 4) * brr(i, 5): Next '============金额列赋值
- Sheet1.[a1].End(4).Offset(3).Resize(n, 6) = brr '======结果位置暂定为源表下方
- End Sub
复制代码
|