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

¶×¥X_¦ý¤£­«ÂжץXªº¸ê®Æ

¶×¥X_¦ý¤£­«ÂжץXªº¸ê®Æ

Dear ¤j¤j

     ¤p§Ì¤S¨Óµo°Ý°ÝÃD¤F~ ¦ý¥ý¯¬¦U¦ì¤j¤j·s¦~§Ö¼Ö~ ¥H«á¦h·ÓÅU ^^
       ¤p§Ìªº°ÝÃD¦b©ó±N¿é¤J¦nªº¸ê®Æ¶×¥X«á,¦ý­n¥ý¤ñ¹ïData¤¤¬O§_¦³­«ÂÐ,¥HÁקK­«ÂЭpºâ

     1.¨Ï¥ÎªÌ·|¦bsheet[¿é¤J]¤¤,±N¸ê®Æ¿é¤J,¦AÂI¿ï{¶×¥X}
     2. ¶×¥X®É¬°ÁקK­«ÂЭpºâ,¨ä±ø¥ó[¤é´Á]and[CPO]and[²Õ§O]¤TªÌ¤£¥i¥H­«ÂÐ

     ¦pªþ¥ó¬°°ÝÃD¿@ÁYªº½d¨Ò   ¶×¥X_¦ý¤£­«ÂÐ.rar (7.09 KB)

¦^´_ 1# hugh0620
  1. Private Sub CommandButton1_Click()
  2. Dim Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet2
  5. ar = .Range(.[B5], .[B5].End(xlDown).Offset(, 2))
  6. For i = 1 To UBound(ar, 1)
  7. mystr = Join(Application.Index(ar, i))
  8. d(mystr) = d.Count
  9. Next
  10. With Sheet1
  11. ar = .Range(.[B5], .[B5].End(xlDown).Offset(, 3))
  12. For i = 1 To UBound(ar, 1)
  13. mystr = Join(Array(ar(i, 1), ar(i, 2), ar(i, 3)))
  14. If d.exists(mystr) = False Then
  15. ReDim Preserve Ay(s)
  16. Ay(s) = Application.Index(ar, i)
  17. s = s + 1
  18. End If
  19. Next
  20. End With
  21. If s > 0 Then .[B65536].End(xlUp).Offset(1, 0).Resize(s, 4) = Application.Transpose(Application.Transpose(Ay))
  22. End With
  23. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

   Dear ¶Wª©¥D ¤j¤j

  ¯uªº«Ü·PÁ§A,«Ü§Öªº´N¸Ñ¨M§Úªº°ÝÃD,§Ú¤]¦³±N±zªº¨C¦æµ{¦¡½X²Ó²Ó´ú¸Õ¨Ã¤F¸Ñ
  ¤j¤j¥Îªº¤è¦¡¬O¥ý±N¸ê®Æ°O¿ý°_¨Ó,¦A¥Î°}¦Cªº¤è¦¡¤ñ¹ï,¬Ý¬O§_¦³¤£¤@¼Ëªº¸ê®Æ
  ¦A±N¤£¤@¼Ëªº¸ê®Æ°O¿ý°_¨Ó,³Ì«á¦A±N¤£¤@¼Ëªº¸ê®Æ©ñ¦bDATA¤¤

  ¦]¬°§Úµ¹¤j¤jªº½d¨Ò¬OÄÝ©ó±ø¥ó¦¡³£¬O¦b¤@°_ªº±¡ªp¤U,¦ý¬O¤j¤j­Y§Úªº±ø¥ó­×§ï¬°¤£¬O³sÄòªº±¡ªp¤U (¥ç·¥±ø¥ó»P±ø¥ó¤¤¶¡¬O¦³¨ä¥L¸ê®Æªº®É­Ô)
  §Ú¦³¸ÕµÛ¦Û¦æ­×§ï,¦ýµL©`¤p§Ì¥\¤O©|²L,µLªk¥Î¤j¤jªº¼Ò¦¡­×§ï¦n
  ¥u¦n¦A½Ð¤j¤j«ü±Ð

  ªþ¥ó¬°°ÝÃD¤§½d¨Ò ¶×¥X_¦ý¤£­«ÂÐ_©µ¦ùÃD.rar (10.68 KB)

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-1-4 15:49 ½s¿è

¦^´_ 3# hugh0620
  1. Private Sub CommandButton1_Click()
  2. Dim Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet2
  5.     ar = .Range(.[B5], .[B5].End(xlDown).Offset(, 2))
  6.         For i = 1 To UBound(ar, 1)
  7.             mystr1 = Join(Application.Index(ar, i))
  8.             d(mystr1) = d.Count
  9.         Next

  10.     With Sheet1
  11.         ar = .Range(.[B5], .[B5].End(xlDown).Offset(, 6))
  12.         For i = 1 To UBound(ar, 1)
  13.             mystr1 = Join(Array(ar(i, 1), ar(i, 2), ar(i, 6)))
  14.             If d.exists(mystr1) = False Then
  15.                 ReDim Preserve Ay(s)
  16.                 Ay(s) = Array(ar(i, 1), ar(i, 2), ar(i, 6), ar(i, 7))
  17.                 s = s + 1
  18.             End If
  19.         Next
  20.     End With
  21.     If s > 0 Then .[B65536].End(xlUp).Offset(1, 0).Resize(s, 4) = Application.Transpose(Application.Transpose(Ay))
  22. End With

  23. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


    Dear ¤j¤j
        ·PÁÂ~ §Úª¾¹D­n§ï­þÃä¤F~ µ{¦¡½X°õ¦æ¤W¤]¨S¦³°ÝÃD~
            ¦ý¬O¤p§Ì¦b´ú¸Õ®É~ ¤S­J«ä¶Ã·Q~ µ²ªG~ §ä¥X¤F¤@­Ó°ÝÃD~
            ´N¬O­YData¤¤~¤@¶}©l´N¨S¦³¸ê®Æ©Î¬O¶È¤@µ§¸ê®Æªº®É­Ô¦n¹³¬O´N·|²£¥Í°ÝÃD~
             For i = 1 To UBound(ar, 1) ¸Ó¦æ«ü¥O¤¤UBound(ar, 1) ´N·|µ¥©ó65536 ³y¦¨¤@ª½¶]¤£°±

        ¦A½Ð¤j¤j½ç±Ð¤@¤U

        ªþ¥ó¬°°ÝÃD¤§½d¥» ¶×¥X_¦ý¤£­«ÂÐ_©µ¦ùÃD2.rar (10.98 KB)
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 4# Hsieh

Dear ¤j¤j
     §Ú¥Î¤@­Ó«Ü²Âªº¤è¦¡¨Ó³B²z·íDATAµL¸ê®Æ®É,©Ò²£¥Íªº°ÝÃD
     ¤£ª¾¹D¤j¤j¬O§_¦³¨ä¥L¤è¦¡
     ³o¬O§Ú¥Î¤j¤jªºµ{¦¡½X­×§ï (¬õ¦â¬°§Ú·s¼Wªº¦a¤è)
Dim Ay()
Set d = CreateObject("Scripting.Dictionary")
With Sheet2
    ar = .Range(.[B5], .[B5].End(xlDown).Offset(, 2))
        If UBound(ar, 1) = 0 Or UBound(ar, 1) = 65536 - 4 Then
            For i = 1 To 1
                mystr1 = Join(Application.Index(ar, i))
                d(mystr1) = d.Count
            Next
        Else
            For i = 1 To UBound(ar, 1)
                mystr1 = Join(Application.Index(ar, i))
                d(mystr1) = d.Count
            Next
        End If    With Sheet1
        ar = .Range(.[B5], .[B5].End(xlDown).Offset(, 6))
        For i = 1 To UBound(ar, 1)
            mystr1 = Join(Array(ar(i, 1), ar(i, 2), ar(i, 6)))
            If d.exists(mystr1) = False Then
                ReDim Preserve Ay(s)
                Ay(s) = Array(ar(i, 1), ar(i, 2), ar(i, 6), ar(i, 7))
                s = s + 1
            End If
        Next
    End With
    If s > 0 Then .[B65536].End(xlUp).Offset(1, 0).Resize(s, 4) = Application.Transpose(Application.Transpose(Ay))
End With
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 7# hugh0620
  1. Private Sub CommandButton1_Click()
  2. Dim Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet2
  5.     ar = .Range(.[B5], .[B65536].End(xlUp).Offset(, 2))
  6.         For i = 1 To UBound(ar, 1)
  7.             mystr1 = Join(Application.Index(ar, i))
  8.             d(mystr1) = d.Count
  9.         Next

  10.     With Sheet1
  11.         ar = .Range(.[B5], .[B65536].End(xlUp).Offset(, 6))
  12.         For i = 1 To UBound(ar, 1)
  13.             mystr1 = Join(Array(ar(i, 1), ar(i, 2), ar(i, 6)))
  14.             If d.exists(mystr1) = False Then
  15.                 ReDim Preserve Ay(s)
  16.                 Ay(s) = Array(ar(i, 1), ar(i, 2), ar(i, 6), ar(i, 7))
  17.                 s = s + 1
  18.             End If
  19.         Next
  20.     End With
  21.     If s > 0 Then .[B65536].End(xlUp).Offset(1, 0).Resize(s, 4) = Application.Transpose(Application.Transpose(Ay))
  22. End With
  23. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh


    ÁÂÁ¤j¤j~ ­ì¨Ó¥u­n§âxldown §ï¦¨xlup´N¥i¥H±Nbugµ¹§JªA
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 7# Hsieh


    Dear ¤j¤j

        ¤p§Ì¤S¤p¥d¼u¤F~ ¤j¤j§Ú»Ý­n±N¸ê®Æ¶×¥X,¦ý¬ODataªº³¡¥÷¤S­n«OÅ@ÁקK³Q¨Ï¥ÎªÌ­×§ï(¨Ï¥ÎªÌ¶È¯à¬Ý)
            ¦pªG§Ú§â¤u§@­¶«OÅ@¤F,¶×¥Xªº®É­Ôµ{¦¡°õ¦æ´N·|²£¥Í¿ù»~
        ³o¼Ëªº°ÝÃD¬O§_¥i¥H¸Ñ¨M©O??

            ªþ¥ó¬°°ÝÃD¤§½d¥» ¶×¥X_¦ý¤£­«ÂÐ_©µ¦ùÃD3.rar (12.41 KB)
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-1-6 19:06 ½s¿è

¦^´_ 9# hugh0620
  1. Private Sub CommandButton1_Click()

  2. Dim Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet2
  5. .Unprotect "password"  'password«ü¤u§@ªí«OÅ@±K½X
  6.     ar = .Range(.[B5], .[B65536].End(xlUp).Offset(, 2))
  7.         For i = 1 To UBound(ar, 1)
  8.             mystr1 = Join(Application.Index(ar, i))
  9.             d(mystr1) = d.Count
  10.         Next

  11.     With Sheet1
  12.         ar = .Range(.[B5], .[B65536].End(xlUp).Offset(, 6))
  13.         For i = 1 To UBound(ar, 1)
  14.             mystr1 = Join(Array(ar(i, 1), ar(i, 2), ar(i, 6)))
  15.             If d.exists(mystr1) = False Then
  16.                 ReDim Preserve Ay(s)
  17.                 Ay(s) = Array(ar(i, 1), ar(i, 2), ar(i, 6), ar(i, 7))
  18.                 s = s + 1
  19.             End If
  20.         Next
  21.     End With
  22.     If s > 0 Then .[B65536].End(xlUp).Offset(1, 0).Resize(s, 4) = Application.Transpose(Application.Transpose(Ay))
  23.     .Protect "password"  'password«ü¤u§@ªí«OÅ@±K½X
  24. End With

  25. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD