字典，跨表判断员工离职状态，帮忙改改代码，不知问题出在哪里

6739912

根据表2中有多名员工，在表1中查找他是否在职或离职，在表2L列显示他的状态。多数人，有字典

下面的代码请大神找找原因

Private Sub CommandButton1_Click()
arr = Sheet1.Range("A1:C" & Sheet1.Range("A65536").End(3).Row)
Set dx = CreateObject("Scripting.Dictionary")
Set dy = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(arr)
    If arr(i, 3) = "离职" Then dx(arr(i, 1)) = ""
Next
kx = dx.keys

j = 0
brr = Sheet2.Range("C1:L" & Sheet2.Range("A65536").End(3).Row)
For i = 2 To UBound(brr)
  For j = 0 To dx.Count - 1
    If brr(i, 1) = kx(j) Then
       dy(brr(i, 10)) = "离职"
    Else
       dy(brr(i, 10)) = "在职"
    End If
  Next j
Next i
ky = dy.keys
[l2].Resize(dy.Count, 1) = Application.Transpose(ky)
Set dx = Nothing
End Sub

^星^

465.zip (78 KB, 下载次数: 10)

2024-3-16 16:04 上传

点击文件名下载附件

gwjkkkkk

Option Explicit
Sub TEST5()
    Dim ar, i&, dic As Object
   
    Set dic = CreateObject("Scripting.Dictionary")
   
    ar = Sheets(1).[A1].CurrentRegion.Value
    For i = 2 To UBound(ar)
        dic(ar(i, 1)) = ar(i, 3)
    Next i
    With [A1].CurrentRegion
        ar = .Value
        For i = 2 To UBound(ar)
            ar(i, UBound(ar, 2)) = dic(ar(i, 3))
        Next i
        .Value = ar
    End With
   
    Set dic = Nothing
    Beep
End Sub

gwjkkkkk

请参考附件。。。

512008404

参与一下！