­ã´£³¡ªL

If .Address = "$A$2" Then
   if not isnumeric(.value) then exit sub '¤£¬O¦³®Ä¼Æ­È©Î¬°¿ù»~­È, ¸õ¥X
   if .value<1 or .value>rows.count then exit sub '¼Æ­È¤£¦b"¦C¼Æ"½d³ò¤º, ¸õ¥X
   lRow = .value

­ã´£³¡ªL

­ìµ{¦¡®Ú¥»¤£¥i¯à°õ¦æ~~
¨S¦³»¡©ú»Ý¨D¬yµ{, ¥ý°µ­Ó¼Ë:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xF As Range, Cr
With Target
    If .Address <> "$A$2" Then Exit Sub
    If .Value = "" Then Exit Sub
    Set xF = [Data!H:H].Find(.Value, Lookat:=xlWhole)
    If xF Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Cr = Array(2, 9, 8, 6, 7, 1, 16, 17, 18, 19, 20)
    For j = 1 To 11
        Cells(5, j + 1) = Sheets("Data").Cells(xF.Row, Cr(j - 1)).Value
    Next j
    Application.EnableEvents = True
End With
End Sub

­ã´£³¡ªL

¦^´_ 5# john2006168

¤£²M¤£·¡, éµÛ¼g:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xF As Range, Cr, DNo1&, dNo2&, xE As Range
With Target
    If .Address <> "$A$2" Then Exit Sub
    If .Value = "" Then Exit Sub
   
    Set xF = [F:F].Find(.Value, Lookat:=xlWhole, SearchDirection:=xlPrevious) '¨ú±o³Ì«á¤@µ§ shipment ref
    If Not xF Is Nothing Then DNo1 = Val(xF(1, -3)) + 1 '±o³Ì«á¤@µ§ shipment ref ªº batch no +1
   
    Set xF = [Data!H:H].Find(.Value, Lookat:=xlWhole)
    If xF Is Nothing Then Exit Sub
   
    Application.EnableEvents = False
    Cr = Array(9, 2, 9, 8, 6, 7, 1, 16, 17, 18, 19, 20, 21)
    Set xE = Cells(Rows.Count, 3).End(xlUp)(2)
    If xE.Row < 5 Then Set xE = [C5]
    For j = 1 To 13
        xE(1, j) = Sheets("Data").Cells(xF.Row, Cr(j - 1)).Value
    Next j
   
    If Not IsDate(xE(1, 2)) Then Exit Sub
    dNo2 = Format(xE(1, 2), "yymm") * 1000 + 1
    xE(1, 0) = Application.Max(DNo1, dNo2)
   
    Application.EnableEvents = True
End With
End Sub

Xl0000285.rar (24.04 KB)

¤U¸ü¦¸¼Æ: 6

2019-3-25 20:12