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

·s¼W»P¬d§äµ{¦¡­×§ï

·s¼W»P¬d§äµ{¦¡­×§ï

¦Û¤v«Ø¤F¤@­Ó®w¦sªí¦pªþ¥ó¡A¤]·Ó®Ñ¦U¼g¤F¤@­Ó·s¼W»P¬d§äªºVBAµ{¦¡¡A·s¼W¬O¨S°ÝÃDªº¡A§Ú¥i¥H¦b¿é¤Jªí³æªº¿é¤JÄæ¦ì¤º¿é¤J¸ê®Æ«á¡A¸ê®Æ·|¦Û°ÊÂà¼g¨ì§Ú«ü©wªº¤u§@ªí¤º¡A¦ý¦pªG¬O¹³ªþÀɨº¼Ë¡A§Ú¦³6­Ó¤u§@ªí¡A­n¦p¦ó§ó§ïµ{¦¡½XÅý¥L¨Ì¿é¤Jªí³æ¤ºªºF2Àx¦s®æ¸Ìªº«¬¸¹¦Û°ÊÂà¼g¨ì¸Ó¤u§@ªí¡C
¥t¤@­Ó°ÝÃD´N¬O¡A¬d§äµLªk°õ¦æ¡A°õ¦æ«á·|¥X²{ °õ¦æ¶¥¬q¿ù»~¿ù»~"1004": 'Range'¤èªk('_Gloable'ª«¥ó)¥¢±Ñ¡A¦]¬°§Ú¬O·Ó®Ñ¼gªº¡A¨S¦³°£¿ùªº¯à¤O¡A§Æ±æ¦U¦ì«e½ú«ü¾É¡C
ÁÙ¦³´N¬O¦b¿é¤Jªí³æ¡A¥i¥HÂê¦í·í§Ú¦b¤J¥X®w³æ¸¹¤º¿é¤Jªº¬O"O"¡B"P"¡B"T"¶}ÀYªº³æ¸¹¡A´N¥u¯à¦b¥X®w¿é¤J¼Æ¶q¡A¦pªG¬O"I"¶}ÀYªº³æ¸¹´NÂê©w¥u¯à¸ü¤J®w¿é¤J¼Æ¶q¡C
¥H¤W¡AÁÂÁ¡C

´ú¸Õ.rar (12.22 KB)

¦^´_ 1# shootingstar
  1. Sub Âà¼g()
  2.     Dim motoHani As Range, sakisht As Worksheet, sakrng As Range
  3.     Set motoHani = [F3].Resize(7)
  4.     If IsError(Application.Match([F2], [B3:B8], 0)) Then MsgBox "¨S¦³ «¬¸¹!! ": Exit Sub
  5.     If [F5] = "" Then MsgBox "¨S¦³ ¤J¥X®w³æ¸¹!! ": Exit Sub
  6.     Set sakisht = Worksheets([F2].Value)
  7.     Set sakrng = sakisht.Range("A" & Rows.Count).End(xlUp).Offset(1)
  8.     sakrng.Resize(, 7).Value = Application.Transpose(motoHani)
  9.     MsgBox "¿é¤J§¹²¦"
  10. End Sub
  11. Sub ¬d§ä()
  12.     Dim motoHani, myRng As Range
  13.     Set motoHani = [F3].Resize(7)
  14.     If IsError(Application.Match([F2], [B3:B8], 0)) Then MsgBox "¨S¦³ «¬¸¹!! ": Exit Sub
  15.     If [F5] = "" Then MsgBox "¨S¦³ ¤J¥X®w³æ¸¹!! ": Exit Sub
  16.     Set myRng = Sheets([F2].Value).Columns(3).Find([F5], LookAt:=xlWhole)
  17.     If myRng Is Nothing Then MsgBox "¨S¦³²Å¦X±ø¥óªº¸ê®Æ¡I":   Exit Sub
  18.     Set myRng = Sheets([F2].Value).Cells(myRng.Row, 1)
  19.     Set myRng = myRng.Resize(, 7)
  20.     motoHani.Value = Application.Transpose(myRng)
  21. End Sub
½Æ»s¥N½X

TOP

¨º¦pªG­n±N«ü©w¸ê®Æ§R°£ªºµ{¦¡À³¸Ó¬O¦p¦ó???

TOP

¦^´_ 3# sheau-lan
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     ¬d§ä "¬d§ä"   'xlword °Ñ¼Æ
  4.     ¬d§ä "§R°£"   'xlword °Ñ¼Æ
  5. End Sub
  6. Sub ¬d§ä(xlword As String)   'xlword ¶Ç»¼ªº°Ñ¼Æ ¬° "¬d§ä" ©Î "§R°£"
  7.     Dim motoHani, myRng As Range
  8.     Set motoHani = [F3].Resize(7)
  9.     If IsError(Application.Match([F2], [B3:B8], 0)) Then MsgBox "¨S¦³ «¬¸¹!! ": Exit Sub
  10.     If [F5] = "" Then MsgBox "¨S¦³ ¤J¥X®w³æ¸¹!! ": Exit Sub
  11.     Set myRng = Sheets([F2].Value).Columns(3).Find([F5], LookAt:=xlWhole)
  12.     If myRng Is Nothing Then MsgBox "¨S¦³²Å¦X±ø¥óªº¸ê®Æ¡I":   Exit Sub
  13.     Set myRng = Sheets([F2].Value).Cells(myRng.Row, 1)
  14.     If xlword = "¬d§ä" Then                           'xlword ¶Ç»¼ªº°Ñ¼Æ ¬° "¬d§ä"
  15.         Set myRng = myRng.Resize(, 7)
  16.         motoHani.Value = Application.Transpose(myRng)
  17.     ElseIf xlword = "¬d§ä" Then                       'xlword ¶Ç»¼ªº°Ñ¼Æ ¬° "§R°£"
  18.         myRng.Resize(, 7).Delete Shift:=xlUp          '¦b³o¸Ì§R°£¸ê®Æ
  19.     End If
  20. End Sub
½Æ»s¥N½X

TOP

½Ð°ÝGBKEE¤j¤j
¨º¦pªG­n¦b¿é¤J§¹²¦«á±N¥H¶ñ¦bªí³æ¤Wªº¸ê®Æ²M°£Åܦ¨ªÅ¥Õ,¥H«K¿é¤J·sªº¸ê®Æ
À³¸Ó«ç»ò°µ®º

TOP

¦^´_ 5# sheau-lan
³Ì«á¥[¤W
  1. [F2].Resize(8) = ""
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD