ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

VBA ·í2­Ó±ø¥ó¤@¼Ë®É¡A¦Û°Ê´M§ä¿é¤J

VBA ·í2­Ó±ø¥ó¤@¼Ë®É¡A¦Û°Ê´M§ä¿é¤J

¦bUserForm1¸Ì¡A1.¦b¨®½ø½s¸¹¤W¿é¤J½s¸¹«á¡A
2.¥q¾÷¤H­û¤S¿é¤J¤H­û½s¸¹«á¡A¦Û°Ê¨Ì§ÇÂà¼gUserForm1¸ê®Æ¦ÜA¡ABÄæ¡C

­«ÂI:°²³]¤u§@ªí¤WA2¤w¿é¤JA201603030001¡AB2¤w¿é¤J¶ÂªQ¤F¡A§Ú¦A«ö¿é¤JUserForm1¸ÌÀY¡A¦b¦P¼Ë¿é¤J¨®½ø½s¸¹_A201603030001¡A¥q¾÷¤H­û_¶ÂªQ®É¡AC3­n¶]¥XB2ªº¥q¾÷¤H­û(¶ÂªQ)¡A´N¬O­n¦Û°Ê¥h´M§ä·í2­Ó±ø¥ó¤@¼Ë®É¡ACÄæÅã²{¹ïÀ³BÄ檺¥q¾÷¤H­û¦W¦r¡C

·PÁ¦ѮvÀ°¦£¸ÑÃD!
ªþÀÉ: 20160304.rar (178.74 KB)

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2016-3-4 16:55 ½s¿è

¦^´_ 1# man65boy
¦P¼ËAB¥X²{²Ä¤G¦¸¥H«á¡ACÄæ´N¸òBÄæ¤@¼Ë?
¬Ý¤£À´³o¼ËCÄæ ¥q¾÷¤H­û(2) ªº·N¸q¬OÔ£¡C

ÁÙ¦³§A¬O­n±qUserForm¿é¤JÁÙ¬O±q¤u§@ªí¿é¤J
§A±Ô­z¹³¬O±qUserForm¡A¥i¬OÀɮ׬O§tWorksheet_Change(±q¤u§@ªí¿é¤J)
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¥»©«³Ì«á¥Ñ man65boy ©ó 2016-3-5 11:46 ½s¿è

¦^´_ 2# stillfish00

¦^µªstillfish00¤j¤j¡A¬O­n±qUserForm¿é¤Jªº¡A¿é¤J«á«ö½T©w¡AÂà¼g¸ê®Æ¨ì¤u§@ªíªº¡C
¨Ì§ÇÂà¼g¨ì¤u§@ªí¤ñ¸û¨S°ÝÃD¡A§xÃøªº¬O·í§Ú¦A¶}±ÒUserForm¿é¤J®É¡A¿é¤Jªº¸ê®Æ¦pªG¸òA.BÄæ¤@¼Ë®É¡A­n¦bCÄæ²£¥Í©MBÄæ¤@¼Ëªº¸ê®Æ(¦ý¤£¯à§â¦bUserForm¿é¤Jªº¸ê®ÆÂà¼g)¡A¥u¯à¦bCÄæ°µ³B²z¡AÁÂÁ¦Ѯv­Ì¶O¤ß!

TOP

¸Õ¸Õ¬Ý:
Private Sub CommandButton1_Click()
    Dim Lst As Integer, R As Integer
    Dim Rng As Range, MH
    Dim sh As Worksheet
    Set sh = Sheets("¥D­¶")
    Lst = sh.[A65536].End(xlUp).Row        '¨ú±o"¥D­¶"ÄæA ³Ì¤U­±«DªÅ¥Õ®æªº¦C¸¹
   
    R = 1
    Set Rng = sh.Range("A2:A" & Lst)
    MH = Application.Match(TextBox1.Value, Rng, 0)
    If Not Application.IsNumber(MH) Then GoTo 101:     '¦pªGTextBox1ªº¸ê®Æ¤£¦bAÄ椤¡÷·s¼W
    R = R + MH
    If Range("B" & R) = TextBox2.Value Then            '§_«h,¤ñ¹ïTextBox2»PBÄæ
'        If Range("C" & MH) <> "" Then GoTo 101:   '??¦pªGCÄæ«DªÅ¥Õ®æ¡÷­n¤£­n·s¼W??
        Range("C" & R) = TextBox2.Value            '§_«hCÄ欰ªÅ¥Õ®æ¡÷CÄæ=BÄæ
        Exit Sub                                   '§Y ¥q¾÷¤H­û(2)=¥q¾÷¤H­û(1)
    End If
   
    '­«ÂФW¦C°Ê§@, ª½¨ì R+1>Lst
    Do
        Set Rng = sh.Range("A" & R + 1 & ":A" & Lst)
        MH = Application.Match(TextBox1.Value, Rng, 0)
        If Not Application.IsNumber(MH) Then GoTo 101:
        R = R + MH
        If Range("B" & R) = TextBox2.Value Then
            Range("C" & R) = TextBox2.Value
            Exit Sub
        End If
    Loop Until R + 1 > Lst
101:
    '·s¼W¤@¦C¸ê®Æ
    Range("A" & Lst + 1) = TextBox1.Value
    Range("B" & Lst + 1) = TextBox2.Value
End Sub

TOP

¦^´_ 4# yen956

ÁÂÁÂyen956¦Ñ®vªº¦^µª¡A¯uªº¬O¤Ó¼F®`¤F¡A­è­è¸Õ¤F´X¦^³£¨S¬Æ»ò°ÝÃD¡A¥u³Ñ¤U¤p§Ìªí¹FÁÙ¦³¤£°÷§¹µ½ªº¦a¤è¡AÀµ½Ð¦Ñ®v¦bÀ°¦£¡A¨Ò¦p:·í¤u§@ªí¤W¦³"¨®½ø½s¸¹"A201603030015©M"¥q¾÷(1)"¶ÂªQ©M"¥q¾÷(2)"¶ÂªQ®É¡A³o¥Nªí¥L¤w¸g°e³f¦^¨Ó¤F¡A¦ý¦]¬°¥L¦b¤£¦P¼Ëªº®É¶¡¤S¥X¥h°e³f¡A©Ò¥HÁÙ­n¦A¦¸¿é¤J"¨®½ø½s¸¹"A201603030015©M"¥q¾÷(1)"¶ÂªQ¡A³o­Ó¸ê®ÆÁÙ­n¨Ì§Ç¦bAÄæ©MBÄæ¤WÂà¼g¡A·í¥L¤S°e³f§¹¦^¨Ó®É¡A¤S­n¦^¨ì¥u¯à¦bCÄæ²£¥ÍBÄæ¹ïÀ³ªº¸ê®Æ¡A³Â·Ð¦Ñ®v¦b¶O¤ß¤F¡AÁÂÁÂ!

TOP

¦^´_ 5# man65boy
  1. Private Sub CommandButton1_Click()
  2. Dim A As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. car = TextBox1 '¨®
  5. man = TextBox2 '¤H
  6. With Sheet2
  7. For Each A In .Range(.[A2], .[A2].End(xlDown))
  8.   If car & man = A & A.Offset(, 1) Then d(A & A.Offset(, 1) & A.Offset(, 2)) = A.Offset(, 2).Address '§ä¨ì¨®¤H
  9.   ad = d(A & A.Offset(, 1))
  10. Next
  11. If d(car & man) = "" Then '¨®¤H¨S¦³«h¥[¦C¸ê®Æ¡A§_«h¶ñ¦^¨®¬ö¿ý
  12. Set A = .Cells(.Rows.Count, 1).End(xlUp)
  13. A.Offset(1, 0) = car
  14. A.Offset(1, 1) = man
  15. Else
  16.   .Range(d(car & man)) = man
  17. End If
  18. End With
  19. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# man65boy
©êºp, ¦Ò¼{¤[©P, Ó𦨤£«K! ©¯¦n¦³¶Wª©ªº±Ï´©!!

TOP

Private Sub CommandButton1_Click()
Dim xNo$, xName$, xR As Range
xNo = TextBox1: xName = TextBox2¡@
For Each xR In Range([A2], [A65536].End(xlUp))
¡@¡@If xR = xNo And xR(1, 2) = xName And xR(1, 3) = "" Then
¡@¡@¡@¡@xR(1, 3) = xName: xR(1, 5) = Now: Exit Sub
¡@¡@End If
Next
[A65536].End(xlUp)(2).Resize(1, 4) = Array(xNo, xName, "", Now)
End Sub

¢°¡D³Ì¦n¦b«e­±¥[¤JÀË¿ù¡G
¡@¡@¡e½s¸¹¡f­­¨î¡e­^¤j¼g¡f+¼Æ¦r¢°¢±½X
¡@¡@¡@If Not xNo Like "[A-Z]############" Then MsgBox "½s¸¹¿ù»~©Î¥¼¿é¤J!": Exit Sub
¡@¡@¡@If xName = "" Then MsgBox "¥q¾÷¦W¥¼¿é¤J!": Exit Sub
¢±¡D¨¾¿ù¬O¢ä¢Ð¢Ï³Ì­«­nªº¡A«Øij¡e¥X¨®¡f»P¡e¦^¨®¡f¤À§O¨â­Ó«ö¶s°õ¦æ¡A
¡@¡@§_«h­Y½s¸¹©Î©m¦W¿é¤J¤@¦r¤§®t¡A¥»¨Ó¬O¦^¨®Åܦ¨¥X¨®¡I

TOP

¡e¥X¨®¡f¡e¦^¨®¡f¤À§O³B²z¡G
  1. Dim xChk&
  2. ¡@
  3. Private Sub CommandButton1_Click()
  4. xChk = 1: Call °O¿ý
  5. End Sub
  6. ¡@
  7. Private Sub CommandButton2_Click()
  8. xChk = 2: Call °O¿ý
  9. End Sub
  10. ¡@
  11. Sub °O¿ý()
  12. Dim xNo$, xName$, xR As Range
  13. xNo = TextBox1: xName = TextBox2
  14. If Not xNo Like "[A-Z]############" Then MsgBox "½s¸¹¿ù»~©Î¥¼¿é¤J!": Exit Sub
  15. If xName = "" Then MsgBox "¥q¾÷¦W¥¼¿é¤J!": Exit Sub
  16. For Each xR In Range([A2], [A65536].End(xlUp))
  17. ¡@¡@If xR = xNo And xR(1, 2) = xName And xR(1, 3) = "" Then
  18. ¡@¡@¡@¡@If xChk = 1 Then MsgBox "¥»¨®¦¸©|¥¼¦^¨®¡A½Ð½T»{!": Exit Sub
  19. ¡@¡@¡@¡@If xChk = 2 Then xR(1, 3) = xName: xR(1, 5) = Now: Exit Sub
  20. ¡@¡@End If
  21. Next
  22. If xChk = 1 Then [A65536].End(xlUp)(2).Resize(1, 4) = Array(xNo, xName, "", Now): Exit Sub
  23. If xChk = 2 Then MsgBox "§ä¤£¨ì¥»¨®¦¸ªº¥X¨®°O¿ý!": Exit Sub
  24. End Sub
½Æ»s¥N½X

¡@
°Ñ¦ÒÀÉ¡G
20160304_v1.rar (179.28 KB)

TOP

¥»©«³Ì«á¥Ñ man65boy ©ó 2016-3-6 13:14 ½s¿è

¦^´_ 6# Hsieh

ÁÂÁÂHsiehªO¤jªº¦^µª¡A«D±`ªº¾A¥Î¡A¤p§Ì·|¥[¥HÀ³¥Î¦¬ÂáA·P®¦¦b¤ß!

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD