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

Excel¤U©Ô¦¡¿ï³æ¿ï¾Ü¯S©w®æ

Excel¤U©Ô¦¡¿ï³æ¿ï¾Ü¯S©w®æ

¦U¦ì«e½ú¦n
¤p§Ì·Q°µ¤@­Ó¤U©Ô¿ï³æ
½Ð°Ý¦p¦ó±N¤U©Ô¿ï³æ¤º®e«ü©w¤@¨Ç¯S©w®æ©O?

¦^´_ 1# cubela


°ÝÃD»¡©ú¤Ó²²¤¡AµLªk¤F¸Ñ»Ý¨D
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ML¤j¤j

§Ú°µ¤@­Ó¤j·§ªº½d¨Ò¡A½Ð¬Ýªþ¥ó¡AÁÂÁÂ
¤U©Ô¿ï³æ¯S©wÄæ¦ì.zip (5.27 KB)

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-9-7 08:57 ½s¿è
ML¤j¤j

§Ú°µ¤@­Ó¤j·§ªº½d¨Ò¡A½Ð¬Ýªþ¥ó¡AÁÂÁÂ
cubela µoªí©ó 2014-9-7 02:02

¦]¬°¦b Excel ¹w³]¨ç¼Æ®w¤¤,§Ú¨Ã¨S¦³§ä¨ì §ì¨úÀx¦s®æ©³¦âªº¨ç¼Æ, ©Ò¥H§Ú¥Î Excel VBA ¨Ó¹ê²{.

ThisWorkBook :
  1. Private Sub Workbook_Open()
  2.   Dim lSRows&, lSRow&
  3.   Dim rTar As Range
  4.   
  5.   bNInt = False
  6.   Set vD = CreateObject("Scripting.Dictionary")
  7.   lSRows = Sheets("¤u§@ªí1").Cells(Rows.Count, 4).End(xlUp).Row
  8.   
  9.   With Sheets("¤u§@ªí2")
  10.     .Activate
  11.     With .ComboBox1
  12.       .Clear
  13.       For lSRow = 2 To lSRows
  14.         Set rTar = Sheets("¤u§@ªí1").Cells(lSRow, 4)
  15.         If rTar.Interior.ColorIndex = 6 Then
  16.           .AddItem rTar
  17.           vD(rTar.Text) = lSRow
  18.         End If
  19.       Next
  20.     End With
  21.   End With
  22.   bNInt = True
  23. End Sub
½Æ»s¥N½X
Module1 :
  1. Public vD, bNInt As Boolean
½Æ»s¥N½X
Sheet2 :
  1. Private Sub ComboBox1_Change()
  2.   If bNInt Then
  3.     Sheets("¤u§@ªí1").Cells(vD(ComboBox1.Text), 5).Copy [C5]
  4.     Sheets("¤u§@ªí1").Cells(vD(ComboBox1.Text), 6).Copy [D5]
  5.   End If
  6. End Sub
½Æ»s¥N½X
¤U©Ô¿ï³æ¯S©wÄæ¦ì-a.zip (16.16 KB)

TOP

¦^´_ 3# cubela
ÅçÃÒ²M³æµLªk¨Ï¥Î¤£³sÄòÀx¦s®æ
¥Î»²§UÄæ¦ì¨ú±o³o¨Ç¤£³sÄò¸ê®Æ°µ¬°ÅçÃÒ²M³æ¨Ó·½
¥H¤u§@ªí2ªºAÄæ§@»²§UÄæ¦ì
¥H1000µ§¸ê®Æ°µ¬°¹w³]
¿ï¨ú¤u§@ªí2!A1:A1000
¿é¤J=IF(ROW()>COUNTA(¤u§@ªí1!$A:$A),"",INDIRECT("¤u§@ªí1!D"&SMALL(IF(¤u§@ªí1!$A$1:$A$1000<>"",ROW($1:$1000),""),ROW($1:1000))))
«öCTRL+SHIFT+ENTER§¹¦¨°}¦C¤½¦¡¿é¤J
­×§ïÁpµ¸¤H¦WºÙªº¤½¦¡
=OFFSET(¤u§@ªí2!$A$1,1,,COUNTA(¤u§@ªí1!$A:$A)-1,)
¦p¦¹¤U©Ô²M³æ´N¯à¨ú±o³o¨Ç¸ê®Æ
­nC¡ADÄæ¦Û°Ê¨ú±o¸ê®Æ
C5=VLOOKUP($B5,¤u§@ªí1!$D:$G,COLUMN(B$1),0)
¦V¥k¦V¤U½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁ¼ӤW¨â¦ì¤j¤jªº¸Ñ»¡
¦ý¥Ø«eÁÙ¤£¯à¤U¸üªþ¥ó
§Ú¥ý¬ã¨s¬ã¨s¡AÁÂÁÂ

TOP

¦^´_  cubela
ÅçÃÒ²M³æµLªk¨Ï¥Î¤£³sÄòÀx¦s®æ
¥Î»²§UÄæ¦ì¨ú±o³o¨Ç¤£³sÄò¸ê®Æ°µ¬°ÅçÃÒ²M³æ¨Ó·½
¥H¤u§@ªí2 ...
Hsieh µoªí©ó 2014-9-9 09:18



    ¤£¦n·N«ä¡AÁÙ¬O¤£À´@@

TOP

¦^´_ 7# cubela

play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  cubela
Hsieh µoªí©ó 2014-9-13 22:06



·PÁ¶Wª©!!!
§Ú·|¤F:victory:
¤£¹L·Q¦A½Ð±Ð¡A¹³³oÃþªº¤½¦¡¬O­n¥ÎKeyªºÁÙ¬O¥i¥H¥Î¿ï¶µ¿ï¥X¨Ó©O?

TOP

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