|
现在我有两个表格,一个是BOM清单,一个是库存明细表,我想达到的目的是当BOM清单中点击确认出库的时候,库存明细表会扣除当日的数量,但是现在发现一个问题,当BOM清单里面的零件名称是一样的时候,库存表扣除就乱了,现在我想再加入一个条件来引用,就是零件规格,麻烦各位大神帮我修改一下,谢谢!!
Sub lqxs()
Dim Arr, i&, rq, wb As Workbook
Dim d, k, t, r1
Set d = CreateObject("Scripting.Dictionary")
Sheet1.Activate
Arr = [a1].CurrentRegion
rq = Arr(1, 8)
For i = 3 To UBound(Arr)
If Arr(i, 4) = "" Then Exit For
d(Arr(i, 4)) = Arr(i, 6)
Next
k = d.keys: t = d.items
Set wb = Workbooks("配件收发存明细表.xlsx")
Dim Sht As Worksheet
For i = 0 To UBound(k)
For Each Sht In wb.Sheets
If InStr(Sht.Name, k(i)) Then
Set r1 = Sht.[a:a].Find(rq, , , 1)
If Not r1 Is Nothing Then
Sht.Cells(r1.Row, 4) = t(i)
End If
End If
Next
Next
Application.DisplayAlerts = False
wb.Close True
Application.DisplayAlerts = True
End Sub
本帖最后由 冥王 于 2015-5-24 15:39 编辑
- Sub lqxs()
- Dim Arr, i&, rq, wb As Workbook
- Dim d, k, t, r1
- Set d = CreateObject("Scripting.Dictionary")
- Sheet1.Activate
- Arr = [a1].CurrentRegion
- rq = Arr(1, 8)
- For i = 3 To UBound(Arr)
- If Arr(i, 4) = "" Then Exit For
- d(Arr(i, 4) & "#" & Arr(i, 5)) = Arr(i, 6)
- Next
- k = d.keys: t = d.items
- Set wb = Workbooks("配件收发存明细表.xlsx")
- Dim Sht As Worksheet
- For i = 0 To UBound(k)
- For Each Sht In wb.Sheets
- If InStr(Sht.Name, Split(k(i), "#")(0)) Then
- If Sht.Cells(2, 2) = Replace(k(i), "#", "") Then
- Set r1 = Sht.[a:a].Find(rq, , , 1)
- If Not r1 Is Nothing Then
- Sht.Cells(r1.Row, 4) = t(i)
- End If
- End If
- End If
- Next
- Next
- Application.DisplayAlerts = False
- wb.Save
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|