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

[µo°Ý] ´M§äªÅ¥Õ®æ¿ï¨ú¾ã¦C,¨Ã¶K¤Wi¦C

[µo°Ý] ´M§äªÅ¥Õ®æ¿ï¨ú¾ã¦C,¨Ã¶K¤Wi¦C

Dear ¤j¤j ¦n,

§Ú»Ý­n¤@­Ó´M§äªÅ¥Õ®æ¨Ã¥H·í¦C¿ï¨ú¾ã¦C,¨Ã¶K¤Wi¦Cªº¥\¯à,
µ{¦¡¥d¦bSet xRng = Range("F:F").Find("") & i
µLªk°õ¦æ,½ÐÀ°¦£¬Ý¤U»yªk­n«ç»ò§ï?
·PÁÂ....
  1. Sub ´ú¸ÕII()
  2. Dim i As String
  3.         Workbooks("³Ì·s®w¦s.xlsx").Sheets("ÀH¤â¶K").Activate
  4.             Range("A4").EntireRow.Select '«ü©wÀx¦s®æ¿ï¨ú¾ã¦C
  5.             Selection.Copy   '''''''''''''''''''''''¨Ì¿ï¨ú¦C¼Æ ½Æ»s«á´¡¤J¦C,Selection¤£¯à¥Î¨ä¥¦¼gªk
  6.                 Workbooks("³Ì·s®w¦s.xlsx").Sheets("¤é»x").Activate
  7.                     i = Workbooks("³Ì·s®w¦s.xlsx").Sheets("¥i¥X¦Ü¤¤¤È").Range("A3").Rows.Count '¬ÝA3¶}©l,¦@¦³´Xµ§¸ê®Æ
  8.                     [color=Red]Set xRng = Range("F:F").Find("") & i[/color]  '±qFªºªÅ¥Õ®æ¿ï¨ú¤@¾ã¦C,¥Hiªºµ§¼Æ ¶K¤Wi¦C
  9.                     xRng.EntireRow.Select '¿ï¨ú¸ÓÄæ¤@¾ã¦C, Select/Selection.Copyªº¥Îªk¤£¯à´«¨ä¥¦¥Îªk,·|¥X¿ù
  10.                      Selection.Paste
  11.                     Application.CutCopyMode = False '¨Ï¨Ó·½ªºcopyªºµê½u°{°Ê°±¤î
  12.                     Application.DisplayAlerts = False '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
  13.                     ActiveWorkbook.Save '**¦sÀɤ£Ãö³¬
  14. End Sub
½Æ»s¥N½X

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2019-11-1 11:54 ½s¿è

¦^´_ 1# PJChen


Set xRng = Range("F:F").Find("") & i

i ¬O¦h¾lªº¡A§ï¦¨¤U­±¬Ý¬Ý¡Aµo°Ý³Ì¦n°t¤Wªþ¥ó¡A¤£µM§O¤H¨S¿ìªkÀ°§A´ú¸Õ

Set xRng = Range("F:F").Find("")
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 2# hcm19522
¯uªº·PÁ§Aªº¦^ÂÐ,¦ýê©ó³o­Ó¥\¯à§Ú»Ý­n¥ÎVBA,©Ò¥H....
ÁöµM§Ú¬Ý¨ç¼ÆªºÀ³¥Î»á§l¤Þ§Ú,¤£¹L§Ú¬Ý¤£À´±z©Ò¦^ÂЪº°õ¦æµ²ªG,¸ò§Úªº¥DÃDªºÃöÁp,
¦p¤£³Â·Ðªº¸Ü,¥i¥H¸Ñ»¡¤U¶Ü?ÁöµM¥¦¤£¯à¥Î¦b§Ú³o¸Ìªº»Ý¨D,¦ý§Ú§Æ±æ¥i¥H硏¨s¤U¥¦ªº¥Îªk¡I

¦^´_ 3# n7822123

­n¶K¨ì"¤é»x"ªº¸ê®Æ¤À¬°3ºØÃþ«¬ ¼t¯Ê,²ÎÎë,¹L¤¹
§Ú¥H²ÎÎ묰½d¥»(³o¸Ì¨C­ÓÀx¦s®æ³£¦³¤½¦¡)·Q§â¤½¦¡¾ã¦C¶K¹L¥h,©Ò¥H­n¿ï¨ú¾ã¦C
«ü©wÀx¦sA4¿ï¨ú¾ã¦C
­ncopyªºµ§¼ÆÀH"¥i¥X¦Ü¤¤¤È"ªºA3¡K.¤@ª½¨ìAÄ檺³Ì«á¤@µ§¸ê®Æ¬°¤î(¸ê®Æ·|ÅÜ°Ê)
¨Ò¦p¡G"¥i¥X¦Ü¤¤¤È"ªºAÄæA3:A6 ¦@¦³4µ§¸ê®Æ
¨º»ò³o¸ÌA4ªº¸ê®Æ´Ncopy¨ì"¤é»x"
¦]¬°"¤é»x"¤u§@ªí¤]¬O¦³¸ê®Æªº,¶Kªº®É­Ô¥HFÄæ§@§PŪ
´M§äªÅ¥Õ®æ¶}©l¶K4µ§
¶K§¹«áªº«áÄòµ{¦¡,§Ú·Q¦Û¤vÀ³¸Ó¥i¥H§¹¦¨,©Ò¥H«á­±ªº§Ú´N¨S¦³´£¤F,
¦ý«e­±©Ò»¡ªº³o¤@¬q,§Ú¸Õ¤F³\¦hºØ¤èªk,³£µLªkÅý¥¦
´M§äªÅ¥Õ®æ¨Ã¥H·í¦C¿ï¨ú¾ã¦C,¨Ã¶K¤Wi¦Cªº¥\¯à
©Ò¥H¤~»Ý­n½Ð ¯àªÌ À°À°¦£¡K.

VBA_´M§äªÅ¥Õ®æ¨Ã¥H·í¦C¿ï¨ú¾ã¦C,¨Ã¶K¤Wi¦C.rar (15.04 KB)

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2019-11-2 09:43 ½s¿è

"¥i¥X¦Ü¤¤¤È" »P "ÀH¤â¶K" ªº®Æ¸¹¦³¦óÃö«Y???
Ãþ«¬>> ¼t¯Ê,²ÎÎë,¹L¤¹ >> ÁÙ¦³¨ä¥¦Ãþ«¬¶Ü???

TOP

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

"¥i¥X¦Ü¤¤¤È" »P "ÀH¤â¶K" ªº®Æ¸¹¦³¦óÃö«Y???

Ans: ÀH¤â¶K ¤ºªº¤TºØÃþ«¬>> ¼t¯Ê,²ÎÎë,¹L¤¹ ¨ä¹ê´N¬O¤TºØ³øªí
¦³³]©w¤½¦¡,¦ýÀɮפ¤§Ú§â¤½¦¡­È¤Æ¤F, "ÀH¤â¶K"ºâ¬O¤@­Ó¤½¦¡½d¥»,©Ò¥H®Æ¸¹¬OµLÃöÁpªº,¥u¬O¬°¤F¤è«KÂà¶K¤½¦¡¥Î,

¦ý3ºØÃþ«¬ªº§@·~®É¶¡¤£¤@¼Ë,¼t¯Ê ¦b¦­¤W°µ,²ÎÎë,¹L¤¹¬O¦b¤U¤È¤~·|°µ,©Ò¥H¤£¯à¦P®É°õ¦æ³o3ºØµ{¦¡

TOP

ÁקK¿ù»~, ¼g±oÅo¶Û¨Ç, ¦h¤@¨Ç°»¿ù:
Sub ½Æ»s¤½¦¡()
Dim R&, T$, xA As Range, xB As Range, xE As Range
With Sheets("¥i¥X¦Ü¤¤¤È")
     R = .[A65536].End(xlUp).Row - 2 '­n½Æ»sªºµ§¼Æ
     If R <= 0 Then MsgBox "µL®Æ¸¹!  ": Exit Sub
     If .[B2] = "¤w½Æ»s" Then MsgBox "¤w½Æ»s¹L, ½Ð½T»{!  ": Exit Sub
     T = .[C2] '¤½¦¡¨Ó·½
     If T = "" Then MsgBox "½Ð¿é¤J¤½¦¡¨Ó·½!  ": Exit Sub
     Set xA = .[A3].Resize(R) '®Æ¸¹½d³ò
End With
'------------------------------------
Set xB = [ÀH¤â¶K!B:B].Find(T, Lookat:=xlWhole)
If xB Is Nothing Then MsgBox "¤½¦¡¨Ó·½¿ù»~!  ": Exit Sub
Set xB = [ÀH¤â¶K!A1:O1].Offset(xB.Row - 1, 0) '¤½¦¡¨Ó·½¦æ
'-----------------------------------
Set xE = [¤é»x!B65536].End(xlUp)(2) 'BÄæ§ä³Ì«á¤@µ§,¨Ã©w¦ì¤U¤@ªÅ¥Õ¦æ(2)
xB.Copy xE(1, 0).Resize(R) '½Æ»s¤½¦¡-->AÄæ(BÄ檺¥ª1®æ)
xE(1, 5).Resize(R) = xA.Value '¶ñ¤J®Æ¸¹-->FÄæ(BÄ檺¥k5Äæ)
[¥i¥X¦Ü¤¤¤È!B2] = "¤w½Æ»s" '¼Ð¥Ü[¤w½Æ»s]ÁקK­«ÂЧ@·~, ­n½Æ»s·sªº®Æ¸¹, ¥ý²M°£B2
End Sub

Xl0000106.rar (16.83 KB)


=======================================

TOP

¦^´_ 7# ­ã´£³¡ªL
·PÁÂ...§Ú´ú¸Õ¬Ý¬Ý...
¤£¹Lºô­¶¤W­ì¥»·|¦³°T®§´£¿ô¥\¯à,³o´X¤Ñµo²{³£¨S¦³´£¿ô¤F...

TOP

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

­ã¤j,
Àɮ׸g´ú¸Õ«á,µo¥Í¤@¨Ç°ÝÃD,¬O§Ú¨S¦³»¡±o©ú¥Õ....
1) ±z­ì¥ý±N¿z¿ï³]©w¦b"¥i¥X¦Ü¤¤¤È"sheet¤¤,¦]¬°³o­Ó¤u§@ªí¤]¬O¤@­Ó³øªí,
©Ò¥H¤£¯à³]©w¦b³o¸Ì,­Y²¾¨ì"ÀH¤â¶K"´N§¹¬ü¤F,¦ý§Ú¤£ª¾¹D«ç»ò§ï??
2) ¥t¥~...¼t¯Ê¡B²ÎÎë¡B¹L¤¹ªº3­Ó¤½¦¡¡A¥¦ªº"¶K®Æ¸¹"³£¦U¦³¨ä¨Ó·½,¨Ã«D¥þ¨Ó¦Û"¥i¥X¦Ü¤¤¤È"sheet,
©Ò¥H¤½¦¡¤]­n­×¥¿
3) ©Ò¦³¶K¦b"¤é»x"¤¤ªº®Æ¸¹,¥¦¬O¨C¤Ñ³£¥i¯àµo¥Í¦ý¤£¤@©wµo¥Í,©Ò¥H¤]¤£¯à³]©w­«½Æ´N¤£¶K.
4)  ²ÎÎë¡B¹L¤¹¦]¬°§@·~®É¶¡¤£¦P¡A©Ò¥H¤£¯à¦b¦P¤@¤äµ{¦¡¤¤
5) §Ú©Ò¦³ªºµ{¦¡³£¼g¦bMacro.xlsm¤¤°õ¦æ¡A©Ò¥H¤£·|¸ò³o¨ÇÀÉ©ñ¦b¤@°_.

¤½¦¡        ¨Ó·½sheet       
¼t¯Ê        ¼t¯Êªí        ³o­Óªíªº¤º®e«Ü½ÆÂø,¤£¦n·N«ä½Ð±zÀ°¦£¤F,©È§A¬Ý±o¤Óµh­W¡I§Úª¾¹D¥H§Aªº¬q¼Æ¬Ý¨ì³o­Ó³øªí¤@©w½¥Õ²´¤F^^
²ÎÎë        ¥i¥X¦Ü¤¤¤È        ½Æ»s¤½¦¡«á,§â¨Ó·½sheetªºAÄæ®Æ¸¹¶K¨ì"¤é»x"ªºFÄæ
¹L¤¹        ¹L¤¹        ½Æ»s¤½¦¡«á,§â¨Ó·½sheetªºBÄæ®Æ¸¹¶K¨ì"¤é»x"ªºFÄæ

Xl0000106_amd.rar (17.48 KB)

TOP

¦^´_ 9# PJChen

¦Û¦æ¬ã¨s¤U, ¦A§ó§ï¬°¹ê»Ú»Ý¨D:
Xl0000106_amd-1.rar (19.5 KB)

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD