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

[µo°Ý] ¦b¦P¤@¦C¦P®É¤ñ¹ï¨âÄæ¸ê®Æ¤èªk

¦^´_ 9# °²­±¶W¤H
¨Ì¾Ú§AªºÀÉ®× ¼g¤U¶i¶¥¿z¿ïªºµ{¦¡
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng(1 To 2) As Range
  4.     Set Rng(1) = Sheet1.Range("a1").CurrentRegion   '¶i¶¥¿z¿ï: ·Ç«h½d³ò  ¨ú±o¬° Sheet1[A1:A3]
  5.     Set Rng(2) = Sheets("³Ì²×µ²ªG").Range("A1").CurrentRegion
  6.     Rng(2) = ""                                     '²M²z¥ý«e¿z¿ïªº¸ê®Æ
  7.     Rng(1).Rows(1).Copy Rng(2).Cells(1)             '½Æ»s ·Ç«hªºÄæ¦ì
  8.     '¸Õ¸Õ¬Ý±N¤W¤@¦æµ{¦¡½Xµù¸Ñ¤£°õ¦æ¬Ý¬Ý
  9.     '¶i¶¥¿z¿ï : ½Æ»s¨ì¥Øªº½d³ò ²Ä1¦C¦³¨º¨Ç¸ê®Æ®wªºÄæ¦ì ´NÅã¥Ü¨º¨Ç¸ê®Æ
  10.     '¦pªÅ¥Õ ·|Åã¥Ü¥þ³¡Äæ¦ìªº¸ê®Æ
  11.     Sheet2.Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Rng(1), Rng(2).CurrentRegion
  12. End Sub
½Æ»s¥N½X

TOP

¦^´_  smouse0220


¯uªº¤ÓÁÂÁ§A¤F¡A³o¼ËÅý¤S¯àÄ~Äò¼g¤U¥h¤F!

²Ä15¦C¤¤ªº                         ...
°²­±¶W¤H µoªí©ó 2012-8-2 13:31


0802.rar (9.52 KB)

TOP

¦^´_ 11# GBKEE

°l¥[°Ý¤@­Ó°ÝÃD
¦pªG­n·j´M123,124¨âµ§¸ê®Æ
123ªº¸ê®Æ¦b¤u§@­¶2¸Ì¥i§ä¨ì
124ªº¸ê®Æ¦b¤u§@­¶3¸Ì¥i§ä¨ì

§Ú¥Î±zªºµ{¦¡½X­×§ï
  1. For a = 2 To 3
  2.             Sheets(a).Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Rng(1), Rng(2).CurrentRegion
  3.         Next
½Æ»s¥N½X
¥[¤F³o°j°é¡A³o¼Ë¥L¨âµ§¸ê®Æ³£·|§ä¨ì¡A¥i¬O·í²Ä2¦¸§ä¨ì®É´N·|§â²Ä1¦¸§ä¨ìªº¸ê®Æ»\±¼¡A¬O§_¥i¥HÅý²Ä¤G2§ä¨ìªº¸ê®Æ¦b²Ä1¦¸ªº¸ê®Æ¤§¤U¥H¦¹Ãþ±À

TOP

¦^´_ 12# smouse0220

sorry...
§Úªºµ¥¯Å¤£¨¬¡AµLªk¤U¸üÀɮסA¬O§_¤è«K¤W¶Ç¨ì§KªÅ
http://imxd.net/

TOP

¦^´_ 14# °²­±¶W¤H

Fyi.
http://imxd.net/file?id=6716

TOP

¦^´_ 13# °²­±¶W¤H
  1. Option Explicit
  2. Sub Ex() '¦³¦h©ó2­Ó¥H¤Wªº¤u§@ªí­n¿z¿ï
  3.     Dim Rng(1 To 3) As Range, a As Integer, Sh_Count As Integer
  4.     Sh_Count = Sheets.Count   '¦¹¬¡­¶Ã¯¤¤¤u§@ªíªºÁ`¼Æ
  5.     Set Rng(1) = Sheets(1).Range("a1").CurrentRegion   '¶i¶¥¿z¿ï: ·Ç«h½d³ò  ¨ú±o¬° Sheet1[A1:A3]
  6.     'Set Rng(2) = Sheets("³Ì²×µ²ªG").Range("A1").CurrentRegion  '§ï¬°¦¹¬¡­¶Ã¯¤¤³Ì«áªº¤u§@ªí
  7.     Set Rng(2) = Sheets(Sh_Count).Range("A1").CurrentRegion
  8.     Rng(2) = ""                                     '²M²z¥ý«e¿z¿ïªº¸ê®Æ
  9.     Rng(1).Rows(1).Copy Rng(2).Cells(1)             '½Æ»s ·Ç«hªºÄæ¦ì
  10.     For a = 2 To Sheets.Count - 1
  11.         Sheets(a).Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Rng(1), Rng(2).Cells(1).Resize(1, Rng(1).Columns.Count)
  12.         Set Rng(2) = Rng(2).Cells(1).End(xlDown).Offset(1) '½Æ»s¨ì¥Øªº½d³ò ©¹¤U²¾°Ê
  13.         If a <> Sheets.Count - 1 Then Rng(1).Rows(1).Copy Rng(2).Cells(1)
  14.         If Rng(3) Is Nothing Then             '¬ö¿ý½Æ»s²Ä2­Ó¤u§@ªí¥H«áªº½Æ»s¨ì¥Øªº½d³òÄæ¦ì ²Ä¤@¦C¦ì¸m
  15.             Set Rng(3) = Rng(2).Cells(1).Resize(1, Rng(1).Columns.Count)
  16.         Else
  17.             Set Rng(3) = Union(Rng(3), Rng(2).Cells(1).Resize(1, Rng(1).Columns.Count))
  18.         End If
  19.     Next
  20.     If Not Rng(3) Is Nothing Then Rng(3).Delete xlUp  '§R°£ ¬ö¿ý½Æ»s²Ä2­Ó¤u§@ªí¥H«áªº½Æ»s¨ì¥Øªº½d³òÄæ¦ì ²Ä¤@¦C¦ì¸m
  21. End Sub
½Æ»s¥N½X

TOP

¦^´_ 16# GBKEE


GBKEEª©¤j¤@ª½³Â·Ð§A¯uªº¤£¦n·N«ä
°w¹ï±z¼gªº¨ºµ{¦¡°µ¤F´ú¸Õ¤j­P¤W¨S°ÝÃD
¦ý¦³ÂI¤p°ÝÃD
·j´M¨ì³Ì«áªº±Æ§Ç·|¨Ì·Ó¥ý¦b­þ­Ó¤À­¶§ä¨ìªº¼g¤J¨ì³Ì«á·|¶Ã±¼¡A¬O§_¥i¥H¯àÅý¸ê®Æ¶¶§Ç©M¤u§@­¶1¤@¼Ë?ÁÂÁÂ
¦hµ§¸ê®Æ§PÂ_(G¤j).rar (16.05 KB)

TOP

¦^´_ 16# GBKEE
½Ð±Ð¡G
        Sheets(a).Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Rng(1), Rng(2).Cells(1).Resize(1, Rng(1).Columns.Count)
¬O¤£¬O­n­×§ï¦¨¡G
        Sheets(2).Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Rng(1), Rng(2).Cells(1).Resize(1, Rng(1).Columns.Count)
§_«h¤U¤@­Ó¤u§@ªí³æ¦p¬°ªÅ¥Õ¡A«h·|¦³¿ù»~°T®§¡C

TOP

¦^´_ 16# GBKEE
¬Ý¤F#17¼Óªº½d¨Ò¡AÀNµM¤j®©¡C¦]¬°½d¨Ò¤¤¨S¦³ªÅ¥Õªí³æ¤§¬G¡C

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-8-6 18:48 ½s¿è

¦^´_ 17# °²­±¶W¤H
¬O­n·ÓSheet1ªº±Æ§Ç¶Ü?
  1. Sub nn()
  2. Dim Ar(), A As Range, B As Range
  3. With Sheets("Sheet1")
  4. For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))  ²Ä¤@­¶A2¥H¤U°µ°j°é
  5.   For Each Sh In Sheets(Array("Sheet2", "Sheet3")) '­ì¸ê®Æ©Ò¦b¤u§@ªí
  6.   With Sh
  7.      For Each B In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))  '¦bA2¥H¤UÀx¦s®æ°µ°j°é
  8.         If B = A Then  '¸ò²Ä¤@­¶AÄæÀx¦s®æ°µ¤ñ¹ï¡A¦pªG²Å¦X
  9.            ReDim Preserve Ar(s)  'ÂX¤j°}¦C
  10.            Ar(s) = Array(B.Value, B.Offset(, 1).Value, B.Offset(, 2).Value, B.Offset(, 4).Value)  '±N­È¼g¤J°}¦C
  11.            s = s + 1  '·Ç³Æ¤U¤@¦¸ÂX¤j°}¦C
  12.         End If
  13.      Next
  14.   End With
  15.   Next
  16.   With Sheets("³Ì²×µ²ªG")
  17.      If s > 0 Then .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(s, 4).Value = Application.Transpose(Application.Transpose(Ar))  '¦pªG°}¦C¦³¼g¤J¡A´N±N°}¦C¼g¤Jµ²ªG
  18.      Erase Ar: s = 0  '²MªÅ°}¦C¡A¨Ã·Ç³Æ¤U¤@­Ó°}¦Cªì©l¤j¤p
  19.   End With
  20. Next
  21. End With
  22. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD