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

[µo°Ý] ¥ý¤£­«½Æ¿z¿ï«á¡A¨Ã¦Û°Ê³]¬°¤U©Ô¦¡¿ï³æ

[µo°Ý] ¥ý¤£­«½Æ¿z¿ï«á¡A¨Ã¦Û°Ê³]¬°¤U©Ô¦¡¿ï³æ

¤j®a¦n¡A¤p§Ì¥Ø«e¦³¤@¤u§@ªí­n¶ñ¼g¡A¦p¹Ï1

¤]´N¬O¤u§@ªí1ªº²ÄG¡BH¡BIÄæ
¦Ó¶ñ¼g¤º®e¥²¶·°Ñ·Ó¤u§@ªí2ªºªí®æ¡A¦p¹Ï2


¦pªG[¥Î³~Ãþ§O(¬ì¥Ø)]¬O--ª««~/®ø¯Ó«~
«h[¥Î³~§O] ¥²©w¬O--·~°È¶O
¥B[¤u§@­pµe] ¤]¥²©w¬O--¤½¥q·~°È

¨ä¹ê·Ç«h¦b[¥Î³~Ãþ§O(¬ì¥Ø)]

§Úªº·Qªk¬O
¬JµM¤u§@ªí1ªº²ÄG¡BH¡BIÄæ¡A­nºCºC¥´¦r¡A¯à§_³]©w¤U©Ô¦¡¿ï³æ
¦Ó¿ï³æªº¤º®e­È¡A¬O±q¤u§@ªí2¥h¤£­«½Æ¿z¿ï
³o¼Ë´N¥i¥H¥Î¤U©Ô¦¡¥h¿ï¡A¤£¥²¥´¦r
¤£­«½Æ¿z¿ï«á¦p¹Ï3


·í§Ú¦b¤u§@ªí1«ö¤U«ö¶s«á
·|¦Û°Ê§â¤u§@ªí1ªºGÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºF
·|¦Û°Ê§â¤u§@ªí1ªºHÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºG
·|¦Û°Ê§â¤u§@ªí1ªºIÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºH

¦ý¬O¡A¤u§@ªí2ªº³o­Óªí®æ¡A¬OÀH®É¦³¥i¯à¼W´îªº
¨C¤@¦¸°õ¦æ¡A³£­n¯à­«·s§PÂ_¡A¨Ã§âªº¿z¿ïµ²ªG²¾°£



¡i²Ä¤G­Ó¦Û°Ê¤Æ§¹¬ü°µªk¡j
´N¬O¬JµM·Ç«h¦b[¥Î³~Ãþ§O(¬ì¥Ø)] --(¤u§@ªí1ªºIÄæ)
¨º«ö¤U«ö¶s«á
·|¦Û°Ê§â¤u§@ªí1ªºIÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºH
µM«áIÄæ¤U©Ô¿ï©w¬Y­Ó­È«á
·|¦Û°Ê§â¤u§@ªí1ªºHÄæ¡A¶ñ¤J¤u§@ªí2¹ï·Óªí®æ¤¤¬Û¹ïÀ³ªº­È
·|¦Û°Ê§â¤u§@ªí1ªºGÄæ¡A¶ñ¤J¤u§@ªí2¹ï·Óªí®æ¤¤¬Û¹ïÀ³ªº­È
(³s°Êªº·§©À)


¤£½×°µªk¤@ ©Î °µªk¤G(§¹¬üªk)
§Ú¥Î¿ý»s¥¨¶°ªº¤è¦¡§ä¥X ¤£­«½Æ¶i¶¥¿z¿ï¨Ã½Æ»s¶K¤W¨ì§Oªº¦a¤è ªº¼gªk
¥i¬O­nÅý¨º­Ó½d³ò¡A³]©w¬°¤U©Ô¦¡¿ï³æªº°Ñ·Ó½d³ò¡A¤×¨ä¥i¯à¤S¬O°ÊºAªº
§Ú¤@ª½·d¤£©w
¨D§U¦U¦ì¤j¤j¤F¡A·PÁÂ

ªþ¤WÀÉ®×
¦Û°Ê³]¤U©Ô¦¡¿ï³æ.rar (23.9 KB)
«¢Åo~¤j®a¦n§r

§Q¥Î"©w¸q¦WºÙ"°ÊºA¤U©Ô²M³æ:
Xl0000170(¤T¼h¤U©Ô²M³æ).rar (17.81 KB)

TOP

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

·PÁª©¥D´£¨ÑÀÉ®×
¦ý¬O§Ú¬Ý«ö¶s¸Ì­±¦ü¥G¨S¦³µ{¦¡½X

¥Ø«e»Ý¨D¬O³o¼Ë
°ò¥»¤W¤u§@ªí1¬O«O«ùªÅªº
¦pªG¦³½ÐÁʳæ¾Ú¶i¨Óªº¸Ü¡A¤~·|¶}©l³vµ§¥´¸ê®Æ¶i¥h
¥´§¹«á·|°Å¨«¡AÅý¤u§@ªí1¤S«O«ù°®²bªÅªºª¬ºA

¥u¬O­n¥´  [¤u§@­pµe        ¥Î³~§O        ¥Î³~Ãþ§O] ªº®É­Ô
¤èªk¤@¡A¥Î¤âºCºC¥´
¤èªk¤G¡A³]©w¸ê®ÆÅçÃÒ¡A³]©w¥h§ä [²M³æ]¸Ì­±ªº½d³ò   ¤u§@­pµe³]©w¤@¦¸         ¥Î³~§O³]©w¤@¦¸          ¥Î³~Ãþ§O³]©w¤@¦¸
               µM«á¨C¤@µ§¸ê®Æ¸Ó¶ñ¤°»ò¡A´N¥h¤U©Ô¿ï¾Ü

¤èªk¤T¡A§Q¥ÎVBA³]¤@­Ó«ö¶s¡A§â¤èªk¤Gªºµ{§Ç³£¦Û°Ê¤Æ¡C
               ­n¨Ï¥Îªº®É­Ô´N«ö¤@¤U²£¥Í¡A¿ï§¹«á°Å¨«¡AÅý¤u§@ªí1¤S«O«ù°®²bªÅªºª¬ºA
               

²{¦b¥Ø¼Ð¦³´X­Ó
¥Ø«e¥i¥H°µ¨ì«ö¤U«ö¶s¡A´N¦Û°Ê§â¿z¿ï¤£­«½Æ«áªºªF¦è¡A¶K¨ì¬YÄæ
¦ý¬O¥i¯à²{¦b¿z¿ï«á¦³¥|ºØ¡A¦ý¬O°Ñ·Óªí¥H«á­×§ï¡A¥i¯à¿z¿ï«á·|Åܤ»ºØ
  1. With Sheets("¤u§@ªí2")
  2.   .UsedRange.Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("¤u§@ªí2").Range( _
  3.         "F1"), Unique:=True
  4. Set Rng =.Range("F1").CurrentRegion
  5. End With
½Æ»s¥N½X
<¥Ø¼Ð¤@>¦p¦óÅýµ{¦¡§PÂ_¡A²{¦b{²M³æ}ªº[¥Î³~Ãþ§O(¬ì¥Ø)]..¿z¿ï«á¦³´XºØ
<¥Ø¼Ð¤G>§PÂ_¦³´XºØ«á¡A±N³o­Ó..´XºØÃþ§O...³]©wµ¹....¤u§@ªí1ªº...[¥Î³~Ãþ§O]¨º¤@Äæ...ªº¤U©Ô¦¡¿ï³æ
  1. Range("I1").Select
  2. With Selection.Validation
  3.     .Delete
  4.     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  5.     xlBetween, Formula1:=Sheets("¤u§@ªí2").Rng   '"=¤u§@ªí2!$C$2:$C$7"
  6.     .IgnoreBlank = True
  7.     .InCellDropdown = True
  8.     .InputTitle = ""
  9.     .ErrorTitle = ""
  10.     .InputMessage = ""
  11.     .ErrorMessage = ""
  12.     .IMEMode = xlIMEModeNoControl
  13.     .ShowInput = True
  14.     .ShowError = True
  15. End With
½Æ»s¥N½X
³]©w¨Ó·½¨ºÃ䦳°ÝÃD  Formula1:=

<¥Ø¼Ð¤T>¦Û°Ê³]©w50¦C³£¦³¤U©Ô¦¡¿ï³æ
  1. Selection.AutoFill Destination:=Range("I1:I50"), Type:=xlFillDefault
½Æ»s¥N½X
<»·µ{¥Ø¼Ð>
­Y¬O¥Ø¼Ð¤@¤G³£¯à¹F¦¨¡A«h   [¥Î³~Ãþ§O] ªº¤U©Ô¿ï¾Ü¬Y¤@Ãþ«á¡A [¤u§@­pµe]¸ò[¥Î³~§O]¥i¯à¦Û°Ê²£¥Í¬Û¹ïÀ³ªº­È¥X¨Ó (¯à°Ñ¦Ò{²M³æ}¤¤ªºªí
¨Ò¦p¡G
[¥Î³~Ãþ§O](IÄæ) ªº¤U©Ô¿ï¾Ü......Â÷¾Àxª÷   ¤§«á
¨º»ò
[¥Î³~§O](HÄæ)¡A·|¦Û°Ê©ñ¤J.....¤H¨Æ¶O
[¤u§@­pµe](GÄæ)¡A·|¦Û°Ê©ñ¤J....¤½¥q·~°È

¦pªGµ{¦¡¦³¿ìªk°µ¨ìªº¸Ü
¦A³Â·Ðª©¥D¤Î¨ä¥L¤j¤j¨ó§U«üÂI¡AÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

¦^´_ 1# iceandy6150


¦Û¤v¸Õ¸Õ§a!
  1. Sub ¤U©Ô¿ï³æ()

  2. '¤u§@ªí2ªºAÄæ
  3. Arr = Range([¤u§@ªí2!A2], [¤u§@ªí2!A65535].End(3))
  4. List$ = ""
  5. For R = 1 To UBound(Arr)  '¥h°£­«½Æ
  6.   If InStr(List, Arr(R, 1)) = 0 Then List = List & "," & Arr(R, 1)
  7. Next
  8. With [G2:G100].Validation  '¤U©Ô®æ¦¡ªº½d³ò¦Û¤v§ï
  9.   .Delete
  10.   .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=List
  11. End With

  12. '¤u§@ªí2ªºBÄæ
  13. Arr = Range([¤u§@ªí2!B2], [¤u§@ªí2!B65535].End(3))
  14. List = ""
  15. For R = 1 To UBound(Arr)  '¥h°£­«½Æ
  16.   If InStr(List, Arr(R, 1)) = 0 Then List = List & "," & Arr(R, 1)
  17. Next
  18. With [H2:H100].Validation  '¤U©Ô®æ¦¡ªº½d³ò¦Û¤v§ï
  19.   .Delete
  20.   .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=List
  21. End With

  22. '¤u§@ªí2ªºCÄæ
  23. Arr = Range([¤u§@ªí2!C2], [¤u§@ªí2!C65535].End(3))
  24. List = ""
  25. For R = 1 To UBound(Arr)  '¥h°£­«½Æ
  26.   If InStr(List, Arr(R, 1)) = 0 Then List = List & "," & Arr(R, 1)
  27. Next
  28. With [I2:I100].Validation  '¤U©Ô®æ¦¡ªº½d³ò¦Û¤v§ï
  29.   .Delete
  30.   .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=List
  31. End With
  32. End Sub
½Æ»s¥N½X
¦Û°Ê³]¤U©Ô¦¡¿ï³æ0518.rar (25.74 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 4# n7822123


   
­ì¼gªk¦³·å²«¡A¥¼¦Ò¼{¶µ¥Ø¥i¯à¦³¤¬¬Û¥]§tªº¦r¦ê

­×¥¿¦p¤U (¬õ¦â³¡¤À)



Sub ¤U©Ô¿ï³æ()

'¤u§@ªí2ªºAÄæ
Arr = Range([¤u§@ªí2!A2], [¤u§@ªí2!A65535].End(3))
List$ = ""
For R = 1 To UBound(Arr)  '¥h°£­«½Æ
  If InStr("," & List & ",", "," & Arr(R, 1) & ",") = 0 Then List = List & "," & Arr(R, 1)
Next
With [G2:G100].Validation  '¤U©Ô®æ¦¡ªº½d³ò¦Û¤v§ï
  .Delete
  .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=List
End With

'¤u§@ªí2ªºBÄæ
Arr = Range([¤u§@ªí2!B2], [¤u§@ªí2!B65535].End(3))
List = ""
For R = 1 To UBound(Arr)  '¥h°£­«½Æ
  If InStr("," & List & ",", "," & Arr(R, 1) & ",") = 0 Then List = List & "," & Arr(R, 1)
Next
With [H2:H100].Validation  '¤U©Ô®æ¦¡ªº½d³ò¦Û¤v§ï
  .Delete
  .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=List
End With

'¤u§@ªí2ªºCÄæ
Arr = Range([¤u§@ªí2!C2], [¤u§@ªí2!C65535].End(3))
List = ""
For R = 1 To UBound(Arr)  '¥h°£­«½Æ
  If InStr("," & List & ",", "," & Arr(R, 1) & ",") = 0 Then List = List & "," & Arr(R, 1)
Next
With [I2:I100].Validation  '¤U©Ô®æ¦¡ªº½d³ò¦Û¤v§ï
  .Delete
  .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=List
End With

End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 3# iceandy6150


·Q¥Î²Ä¤T¼h¿ï³æ, ©¹¦^±Àºâ«e¨â¼h¬ÛÃöÁp¤å¦r, ¦A¦Û°Ê¶ñ¤J???
°ÝÃD:²Ä¤T¼h¿ï³æ©Ò¹ïÀ³ªº¦³¨â­Ó¥H¤W, ¦p¦ó§PÂ_Äݤ_­þ¤@­Ó???

2¼Óµ¹ªº¬Oº¥¶i¦¡ªº°ÊºA¿ï³æ, ¿ï²Ä¤@¼h«á, ¦Û°Ê§PÂ_²Ä¤G¼h²M³æ, ¦A¦Ü²Ä¤T¼h,
¶V¿ï¶V¤Ö, ¤]¤£¶·¥´¦r!!!

¦Ü¤_ªí1, ¥i¹w«Ø1~200¦CÅçÃÒ²M³æ,
¿é¤J§¹¦¨-Âà¥X«á, ¥ÎCLEARCONTENTS²MªÅ¤º®e, §Y¥iÄ~Äò¿é¤J·sªº¸ê®Æ,
´X¦æµ{¦¡½X§Y¥i~~

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

TOP

¦^´_ 5# n7822123

·PÁ§AªºÀ°¦£¡A§Ú¦Û¤v«á¨Ó¦³§ä¨ì¤èªk¤F¡A·|¶K¦b³Ì¤U­±ªº¦^À³
¦A»P¤j¤j±z¤À¨É¥æ¬y
«¢Åo~¤j®a¦n§r

TOP

¥»©«³Ì«á¥Ñ iceandy6150 ©ó 2020-5-18 23:12 ½s¿è

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

ª©¥D±z¦n¡A§Ú«á¨Ó·Q¤F­Ó¤g¤èªk

§Úª¾¹D¥i¥H¿ï©w¤@­Ó¤j½d³ò¡Aµ¹¥L©w¸q¦WºÙ
µM«áVLOOKUP³o­Ó¨ç¼Æ¥u¯à§ä...¤@­Ó½d³ò¤º¡A³Ì¥ªÃ䨺Äæ·í¼Ð·Ç

©Ò¥H­ì¥» AÄæ BÄæ CÄæ¡A´N½Æ»s¨ì§Oªº¦a¤è¡AÅܦ¨CÄæ¡ABÄæ¡AAÄæ
µM«áVLOOKUP CÄæ¡A¥i¥H§ä¨ì¬Û¹ïÀ³ªºA¸òB
(¨ä¹ê¥ÎFIND¦n¹³¤]¥i¥H)

¦Ü©ó¤U©Ô¦¡¿ï³æªº¤º®e¡A¦]¬°¶i¶¥¿z¿ï¹L«á¡A§â¿z¿ï¹Lªº½d³ò¡A©w¸q¦WºÙ
¦A§â³o­Ó¦WºÙµ¹¤U©Ô¦¡¿ï³æ (¨Ó·½¬°ÅܼÆ)
³oÃä­n°t¦X INDIRECT ³o­Ó»yªk¡A¤~¯à¥¿±`¨Ï¥Î¡A¤]¬Oºô¸ô¬d¨ìªº

¥H¤U¬Oµ{¦¡½X
¸ò´ú¸ÕÀÉ®×
(¥Ø«e¦Û°Ê²£¥Í¤U©Ô²M³æ¥u³]©w5®æ)
  1. Private Sub CommandButton1_Click()
  2.     Range("A2:C20").Select
  3.     Selection.ClearContents
  4.     With Selection.Validation
  5.         .Delete
  6.         .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
  7.         :=xlBetween
  8.         .IgnoreBlank = True
  9.         .InCellDropdown = True
  10.         .IMEMode = xlIMEModeNoControl
  11.         .ShowInput = True
  12.         .ShowError = True
  13.     End With
  14.    
  15.     Range("C2").Select
  16.    
  17. End Sub


  18. '*******************************************************************************


  19. Private Sub CommandButton2_Click()
  20. Dim a, b, c, d

  21. Sheets("¤u§@ªí2").Select

  22. With Sheets("¤u§@ªí2")

  23.     '¨C¦¸°õ¦æ¥ý±N³Ì¥kÃä¥|Äæ¬å¥ú
  24.     .Columns(.Columns.Count).Delete Shift:=xlShiftLeft
  25.     .Columns(.Columns.Count - 1).Delete Shift:=xlShiftLeft
  26.     .Columns(.Columns.Count - 2).Delete Shift:=xlShiftLeft
  27.     .Columns(.Columns.Count - 3).Delete Shift:=xlShiftLeft


  28.     '[¥Î³~Ãþ§O]¥ý½Æ»s¨ì³Ì¥kÃä­Ë¼Æ²Ä¤TÄæ
  29.     .Columns("C:C").Select
  30.     Selection.Copy
  31.     Sheets("¤u§@ªí2").Columns(.Columns.Count - 2).PasteSpecial (xlPasteAll)
  32.     Application.CutCopyMode = False


  33.     '[¥Î³~§O]¥ý½Æ»s¨ì³Ì¥kÃä­Ë¼Æ²Ä¤GÄæ
  34.     .Columns("B:B").Select
  35.     Selection.Copy
  36.     Sheets("¤u§@ªí2").Columns(.Columns.Count - 1).PasteSpecial (xlPasteAll)
  37.     Application.CutCopyMode = False
  38.    
  39.    
  40.     '[¤u§@­pµe]¥ý½Æ»s¨ì³Ì¥kÃä­Ë¼Æ²Ä¤@Äæ
  41.     .Columns("A:A").Select
  42.     Selection.Copy
  43.     Sheets("¤u§@ªí2").Columns(.Columns.Count).PasteSpecial (xlPasteAll)
  44.     Application.CutCopyMode = False
  45.    

  46.     '¶i¶¥¤£­«½Æ¿z¿ï--©ñ¨ì...³Ì¥kÃä­Ë¼Æ²Ä¥|Äæ
  47.     .Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns(.Columns.Count - 3), Unique:=True
  48.      Application.CutCopyMode = False
  49.      
  50.     a = Sheets("¤u§@ªí2").Columns(.Columns.Count - 3).End(xlDown).Row '¬d¬Ý(¬ì¥Ø)¦³¦h¤Ö­Ó¼Æ¶q
  51.     b = .Columns.Count - 3 'b¬°²Ä´XÄæ(³Ì¥kÃä­Ë¼Æ²Ä¥|Äæ)
  52.      
  53.      
  54.     '³]©w--©w¸q½d³ò
  55.     ActiveWorkbook.Names.Add Name:="abc", RefersToR1C1:="=¤u§@ªí2!R2C" & b & ":R" & a & "C" & b
  56.     ActiveWorkbook.Names("abc").Comment = ""
  57.      
  58.      
  59.     '³]©w¤U©Ô¦¡²M³æ
  60.     Sheets("¤u§@ªí1").Select

  61.         Range("C2").Select
  62.         With Selection.Validation
  63.             .Delete
  64.             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  65.             xlBetween, Formula1:="=INDIRECT(""abc "")"
  66.             .IgnoreBlank = True
  67.             .InCellDropdown = True
  68.             .InputTitle = ""
  69.             .ErrorTitle = ""
  70.             .InputMessage = ""
  71.             .ErrorMessage = ""
  72.             .IMEMode = xlIMEModeNoControl
  73.             .ShowInput = True
  74.             .ShowError = True
  75.         End With

  76.     '½Æ»s¤U©Ô¦¡²M³æ¨ì§O®æ
  77.      Selection.AutoFill Destination:=Range("C2:C5"), Type:=xlFillDefault


  78.     '³]©w ©w¸q½d³ò
  79.      a = Sheets("¤u§@ªí2").Columns(.Columns.Count - 2).End(xlDown).Row  '¬d¬Ý³o±i°Ñ·Óªí¦³¦h¤Ö[¥Î³~Ãþ§O]¡A³o±iªíªº©³³¡¨ì¦h§C
  80.      b = .Columns.Count - 2 'b¬°²Ä´XÄæ(³Ì¥kÃä­Ë¼Æ²Ä¤TÄæ...[¥Î³~Ãþ§O])
  81.    
  82.         Sheets("¤u§@ªí2").Select
  83.         ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:="=¤u§@ªí2!R2C" & b & ":R" & a & "C" & (b + 2)  '³o­Ó½d³ò¦³¤TÄæ¡A±qb¨ìb+2
  84.         ActiveWorkbook.Names("data").Comment = ""

  85.     '³]©w¦Û°Ê¥X²{-¤èªk2¡A­Y¹ï·Óªí¤¤¬°ªÅ®æ¡AÅã¥ÜªÅ®æ¡A­Y¤u§@Ãþ§O(¬ì¥Ø)¥¼¿ï¡AÅã¥ÜªÅ®æ
  86.      '(±q©w¸q¦nªº½d³òdata¤¤¥h§ä¨ì½d³ò¤¤ªº²Ä2Äæ¡A©ñ¨ìB2---B2­n©ñ¥Î³~§O¡Adataªº²Ä2Äæ¤]¬O¥Î³~§O)
  87.      
  88.      '¥ý³]©w[¥Î³~§O]
  89.          Sheets("¤u§@ªí1").Select
  90.          Range("B2").Select
  91.          ActiveCell.FormulaR1C1 = _
  92.              "=IFERROR(IF(VLOOKUP(RC3,data,2,0)=0,"""",VLOOKUP(RC3,data,2,0)),"""")"
  93.         '½Æ»s¤½¦¡¨ì§O®æ
  94.          Selection.AutoFill Destination:=Range("B2:B5"), Type:=xlFillDefault
  95.         
  96.       '¦A³]©w[¤u§@­pµe]---A2­n©ñ¥Î[¤u§@­pµe]¡Adataªº²Ä3Äæ¤]¬O[¤u§@­pµe])
  97.          Sheets("¤u§@ªí1").Select
  98.          Range("A2").Select
  99.          ActiveCell.FormulaR1C1 = _
  100.              "=IFERROR(IF(VLOOKUP(RC3,data,3,0)=0,"""",VLOOKUP(RC3,data,3,0)),"""")"
  101.         '½Æ»s¤½¦¡¨ì§O®æ
  102.          Selection.AutoFill Destination:=Range("A2:A5"), Type:=xlFillDefault
  103.         
  104. End With

  105. Range("C2").Select


  106. End Sub
½Æ»s¥N½X
¦Û°Ê³]¤U©Ô¦¡¿ï³æ.rar (31.51 KB)

·PÁ¦U¦ì
«¢Åo~¤j®a¦n§r

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-19 11:17 ½s¿è

¦^´_ 1# iceandy6150

¡i²Ä¤G­Ó¦Û°Ê¤Æ§¹¬ü°µªk¡j
´N¬O¬JµM·Ç«h¦b[¥Î³~Ãþ§O(¬ì¥Ø)] --(¤u§@ªí1ªºIÄæ)
¨º«ö¤U«ö¶s«á
·|¦Û°Ê§â¤u§@ªí1ªºIÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºH
µM«áIÄæ¤U©Ô¿ï©w¬Y­Ó­È«á
·|¦Û°Ê§â¤u§@ªí1ªºHÄæ¡A¶ñ¤J¤u§@ªí2¹ï·Óªí®æ¤¤¬Û¹ïÀ³ªº­È
·|¦Û°Ê§â¤u§@ªí1ªºGÄæ¡A¶ñ¤J¤u§@ªí2¹ï·Óªí®æ¤¤¬Û¹ïÀ³ªº­È
(³s°Êªº·§©À)


­ì¨Ó§Aªº»Ý¨D¥u¬O³o»ò²³æ~ 10¤ÀÄÁ·d©w
³o§Ú±`±`À°¤½¥qªº¦P¨Æ°µ
¤£»Ý©R¦W¦WºÙ¡B¤£¥Î¨ç¼Æ
¬Ý§A¦³¨S¦³¤ß·Q¾Ç§O¤Hªº°µªk



xlsª©¥»¬Oµ¹Âª©Excelªº¤H¨Ï¥Îªº¡A
2007¥H«á½Ð¶}xlsmª©¥»


¦Û°Ê³]¤U©Ô¦¡¿ï³æ0519.rar (46.43 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

1) ¦pªGªí2ªºCÄæ³£¬O¤£­«ÂЪº, ¨ºª½±µ¥Î³o¨Ó³]©w²Ä¤T¼h¿ï³æ,
   ¤S¦pªG¸ê®Æ¨Ã¤£ÅÜ°Ê©ÎÅܰʤÖ, «h¥i©T©w½d³ò©w¸q¦WºÙ,
   ­Y®É¦³ÅÜ°Ê(­«ÂI:¤£­«ÂÐ), ¥Î°ÊºA©w¸q¦WºÙ§Y¥i¸Ñ¨M, ¦ó¶·¨C¦¸³£­n¶]VBA°j°é!!!
   ³o¼Ë¥ÎFIND´N¥i©w¦ì§ä¥X«e¨â¼h¸ê®Æ~
2) ¦pªGCÄæ¬O·|­«ÂЪº, ¨º¥Î²Ä¤T¼h¿ï³æ§ä¹ïÀ³ªº«e¨â¼h, ´N¤£¬O¨º»ò·Ç½T,
   ¦óªp¦pªGCÄ榳´X¦Ê©Î¤W¤d¦æ, ¨C¦¸³£­n±qªø¤Sªøªº¿ï³æ¤¤¥h¿ï¨ú©Ò­nªº, ·Æ¹«·Æ¤W·Æ¤U__¨Ã¤£¤ÓÁo©ú,¤]®e©ö¿ï¿ù­«¨Ó;
   ¦A«h, ´X¦Ê¤W¤d¦æªº¤º®e¥Î¦r¨åÀÉÀ½¦¨¦r¦ê, ¦A¶ë¶i²M³æ¤¤, ·|¤£·|¶W¹L¤å¦rªø«×­­¨î???

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD