[µo°Ý] ½Ð°ÝVBA ±ø¥ó§PÂ_¦¡ªº¨Ï¥Î¤èªk?¨Ï¥Î¤èªk

john2006168

³o­Ó¬O¦b½×¾Â¾Ç²ß¨ìªºµ{¦¡,¦ý¬O¦pªGA2¸ê®Æ¿ù»~,µ{¦¡´N¤£¯à°õ¦æ,½Ð°Ý­n«ç»ò­×§ï?


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rTar As Range ' ¶ñ¸ê®Æ¼ÐªºÀx¦s®æAÄæ
  
  With Target
    If .Address = "$A$2" Then
      lRow = dShi(.Text)
      Application.EnableEvents = False ' Åܧó¸ê®Æ¤¤,Ãö³¬Ä²µoChangeµ{§Ç'dShi(CStr(.Cells(lRow, 8))) = lRo
        .Offset(, 1) = shDat.Cells(lRow, 2) ' B2
        .Offset(, 2) = shDat.Cells(lRow, 9) ' c2
        .Offset(, 3) = shDat.Cells(lRow, 8)
        .Offset(, 4) = shDat.Cells(lRow, 6)
        .Offset(, 5) = shDat.Cells(lRow, 7)
        .Offset(, 6) = shDat.Cells(lRow, 1)
        .Offset(, 7) = shDat.Cells(lRow, 16)
        .Offset(, 8) = shDat.Cells(lRow, 17)
        .Offset(, 9) = shDat.Cells(lRow, 18)
        .Offset(, 10) = shDat.Cells(lRow, 19)
        .Offset(, 11) = shDat.Cells(lRow, 20)
        
        lShrRow = lShrRow + 1
        Set rTar = shShr.Cells(lShrRow, 1) ' [A¥½µ§Äæ¼Æ]
      
        ' ¨ä¾l½Ð¦Û¦æ½Õ¾ã¶ñ¤J
        
        
      Application.EnableEvents = True ' ¸ê®ÆÅܧ󧹲¦,«ì´_¥iIJµoChangeµ{§Ç
    End If
  End With
End Sub

­ã´£³¡ª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

john2006168

¦^´_ 2# ­ã´£³¡ªL


    ¤£¦n·N«ä,¤£ª¾¹D­þ¸Ì¥X¿ù,ÁÙ¬O¤£¦æ,À°¦£¬Ý¬Ýªþ¥ó

­ã´£³¡ª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

john2006168

¦^´_ 4# ­ã´£³¡ªL


    ²{¦b®t³Ì«á1¨B,¦pªG§Ú·Q¨ú¨ìªº¸ê®Æcopy ¨ìÄæB,²{¦³ªºbtach no ¦]¬°B5¦V¤Uªº¸ê®Æ¸g±`¤£¦P

§Úª¾¹D­n¼gFor Each cell In Range([b5], [b65536].End(xlUp)),¨ä¥L´N¤£¬O«Ü²M·¡,§Æ±æ¦Ñ®v¥i¥H¦A¦¸À°¦£.¡@......

­ã´£³¡ª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

john2006168

¦^´_ 6# ­ã´£³¡ªL


   ¥u¬O·Q¦³¬Û¤ù¤ºªºµ²ªG,¦ý¬O«D±`·PÁÂ,¦hÁ©â®É¶¡¦^µª°ÝÃD,,¥i¥H¦Û¤vºCºC¾Ç.