|
楼主 |
发表于 2019-12-17 18:56
|
显示全部楼层
您好大神,早晨看到了您的回复后,还没来及答谢我就着急去工作了,刚下班回家【我真不是伸手党,我也是自己从网上慢慢学的拼写起来的】能力实在是有限,我刚才运行了一下,还是有问题出现您能再帮忙看看吗?
Sub 确定单价()
Dim rng As Range, Sh As Worksheet, Flag As Boolean
If [A2] = "销售出库清单" Then Set Sh = Sheets("销售记录") Else Set Sh = Sheets("滚子滚针进价成本表")
xm = [b3] '客户姓名:
If xm = """" Then Exit Sub
[d6:d13].ClearContents
For Each rng In [b6:b13]
If rng <> "" Then
Flag = False
这一步,这一步"Arr = Sh.[a1].CurrentRegion"这一步,这一步
For i = UBound(Arr) To 1 Step -1 '从下往上找,找最近成交案例
If Arr(i, 2) = xm And Arr(i, 3) = rng Then rng.Offset(0, 2) = Arr(i, 5): Flag = True: Exit For '如果找到
Next
If Flag = False Then '如果无成交案例,则在滚子滚针进价成本表中匹配
Arr = Sheets("滚子滚针进价成本表").[a1].CurrentRegion
For i = 1 To UBound(Arr)
If Arr(i, 1) = rng Then rng.Offset(0, 6) = Arr(i, 2): Exit For '如果找到
Next
End If
End If
Next
If [A2] = "采购入库清单" Then Set Sh = Sheets("采购记录")
xm = [b3] '供货商姓名:
If xm = """" Then Exit Sub
[d6:d13].ClearContents
For Each rng In [b6:b13]
If rng <> "" Then
Flag = False
Arr = Sh.[a1].CurrentRegion
For i = UBound(Arr) To 1 Step -1 '从下往上找,找最近成交案例
If Arr(i, 2) = xm And Arr(i, 3) = rng Then rng.Offset(0, 2) = Arr(i, 5): Flag = True: Exit For '如果找到
Next
If Flag = False Then '如果无成交案例,则在滚子滚针进价成本表中匹配
Arr = Sheets("滚子滚针进价成本表").[a1].CurrentRegion
For i = 1 To UBound(Arr)
If Arr(i, 1) = rng Then rng.Offset(0, 5) = Arr(i, 2): Exit For '如果找到
Next
End If
End If
Next
End Sub
|
|