求大神帮忙用VBA写个代码,自动引用以前的数据

JIUYINI

求大神帮忙用VBA写个代码，当我在B列录入样版单号是，就自动查询以前有没有录入过相同的单号，有的就自动在后面的单元格填上和之前相同的数据

3190496160

Private Sub Worksheet_Change(ByVal T As Range)
If T.Row > 2 And T.Column = 2 Then
    If T.Count > 1 Then Exit Sub
    If T.Value = "" Then Exit Sub
    Dim d As Object
    Set d = CreateObject("scripting.dictionary")
    r = T.Row - 1
    ar = Range("b3:r" & r)
    For i = 1 To UBound(ar)
        If Trim(ar(i, 1)) <> "" Then
            d(Trim(ar(i, 1))) = i
        End If
    Next i
    m = d(Trim(T.Value))
    If m <> "" Then
        For j = 2 To UBound(ar, 2)
            Cells(r + 1, j + 1) = ar(m, j)
        Next j
    End If
End If
End Sub

3190496160

样品.rar (91.86 KB, 下载次数: 10)

2020-8-9 16:20 上传

点击文件名下载附件

准提部林

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xF As Range, C
With Target
     If .Count > 1 Or .Row < 2 Or .Column <> 2 Or .Item(1) = "" Then Exit Sub
     Set xF = [B:B].Find(.Value, Lookat:=xlWhole)
     If xF Is Nothing Then Exit Sub
     If xF.Address = .Address Then Exit Sub
     For Each C In Array(2, 3, 5, 6, 8, 9)
         .Cells(1, C) = xF(1, C).Value
     Next
End With
End Sub