统计不匹配数量提示下标越界

书中苹果

Sub 数据更新()

Dim wb As Workbook        '订单工作簿
Dim sb As Workbook        '源工作簿
Dim DingDan As Worksheet      '日出货订单
Dim Sht As Worksheet      '源工作簿源表
Dim Arr, Brr, x$, y, d, i&, j&, Nocount&, z, e

Application.ScreenUpdating = False

'指定工作簿和工作表
Set wb = ThisWorkbook
Set sb = Workbooks.Open(wb.Path & "\Source.xlsx")
Set DingDan = wb.Worksheets("日出货订单")
Set Sht = sb.Worksheets("Sheet1")
Set d = CreateObject("Scripting.Dictionary")
Arr = Sht.[a1].CurrentRegion
For i = 2 To UBound(Arr)
     x = Arr(i, 9)
     For j = 14 To UBound(Arr, 2)
         y = Arr(1, j)
         If d.exists(x) = False Then Set d(x) = CreateObject("Scripting.Dictionary")
         d(x)(y) = Arr(i, j)
     Next
Next

sb.Close False
DingDan.[h4:gp5000].ClearContents
Brr = DingDan.[a1].CurrentRegion
For i = 3 To UBound(Brr)
    x = Brr(i, 206)
    If d.exists(x) Then
      For j = 8 To 197
          y = Brr(2, j)
          If d(x).exists(y) Then Brr(i, j) = d(x)(y)
      Next
    End If
Next

DingDan.[a1].CurrentRegion = Brr
Application.ScreenUpdating = True

Set e = CreateObject("Scripting.Dictionary")
For i = 3 To UBound(Brr)
x = Brr(i, 206)
Set e(x) = CreateObject("scripting.dictionary")
Next

For z = 2 To UBound(Arr)
x = Arr(i, 9)
If e.exists(x) = False Then Nocount = Nocount + 1
Next

If Nocount <> 0 Then
MsgBox ("未匹配到源表产品数量：" & Nocount)
Else
MsgBox "取数完毕！"
End If

Set wb = Nothing
Set sb = Nothing
Set DingDan = Nothing
Set Sht = Nothing

End Sub

和表格关闭有关吗？还要在写上打开此文件表的语句吗？

abc123281

建议上传附件   并说明详细要求  目的

书中苹果

abc123281 发表于 2018-6-25 12:07
建议上传附件   并说明详细要求  目的

主要的目的是实现从source表中取数到sample表中，并统计源表中的产品有多少没有被匹配到，以便检查源表中的产品是否全部正常导入sample表中；

yaojil

For z = 2 To UBound(Arr)
x = Arr(z, 9)
If e.exists(x) = False Then Nocount = Nocount + 1
Next

未匹配到源表产品数量 240

书中苹果

yaojil 发表于 2018-6-25 12:39
For z = 2 To UBound(Arr)
x = Arr(z, 9)
If e.exists(x) = False Then Nocount = Nocount + 1

是哦，哎呀，我真是太不小心了，难怪找来找去都没有结果。谢谢帮忙检查，非常感谢！