|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
测试
对比匹配.zip
(34.09 KB, 下载次数: 5)
Sub test()
Dim sz1, sz2, aa1, aa2, aa3, bb1, cc1
Sheet1.Range(Sheet1.Cells(3, 9), Sheet1.Cells(Sheet1.Cells(Rows.Count, 3).End(3).Row, 52)).ClearContents
sz1 = Sheet1.Range(Sheet1.Cells(2, 3), Sheet1.Cells(Sheet1.Cells(Rows.Count, 3).End(3).Row, 52))
sz2 = Sheet2.Range(Sheet2.Cells(2, 3), Sheet2.Cells(Sheet2.Cells(Rows.Count, 3).End(3).Row, 8))
For aa1 = 2 To UBound(sz1)
For bb1 = 1 To UBound(sz2)
If sz1(aa1, 1) = sz2(bb1, 1) Then
For aa2 = 10 To 22
If sz1(1, aa2) = sz2(bb1, 4) Then
sz1(aa1, aa2) = sz1(aa1, aa2) + sz2(bb1, 6)
aa3 = aa3 + sz2(bb1, 6)
Exit For
End If
Next
End If
Next
cc1 = sz1(aa1, 6)
If aa3 >= cc1 Then
sz1(aa1, 37) = "调整满足"
For aa2 = 10 To 22
If sz1(aa1, aa2) - cc1 >= 0 Then
sz1(aa1, aa2 + 13) = cc1
sz1(aa1, aa2 + 28) = sz1(aa1, aa2) - cc1
sz1(aa1, 36) = sz1(aa1, 36) & sz1(1, aa2) & ":" & sz1(aa1, aa2 + 13) & ";"
cc1 = 0
Exit For
Else
sz1(aa1, aa2 + 13) = sz1(aa1, aa2)
If sz1(aa1, aa2 + 13) <> Empty Then sz1(aa1, aa2 + 28) = 0
If sz1(aa1, aa2 + 13) <> Empty Then sz1(aa1, 36) = sz1(aa1, 36) & sz1(1, aa2) & ":" & sz1(aa1, aa2 + 13) & ";"
cc1 = cc1 - sz1(aa1, aa2)
End If
Next
Else
sz1(aa1, 37) = "无法调整"
For aa2 = 10 To 22
If sz1(aa1, aa2) <> Empty Then sz1(aa1, 36) = sz1(aa1, 36) & sz1(1, aa2) & "余量:" & sz1(aa1, aa2) & ";"
Next
End If
aa3 = 0
Next
|
评分
-
1
查看全部评分
-
|