[µo°Ý] ¦p¦ó¦Û°Ê¨ú¥N·sªº¤º®e¸ê®Æ?

winston219

¦pªþÀÉ¡AÁÂÁ¤j¤j! book.rar (5.91 KB)

¤U¸ü¦¸¼Æ: 15

2010-11-25 20:04

Hsieh

¥u¦³¦b¥t¤@Äæ¦ìÅã¥ÜÀ³¨ú¥Nªº¹ê»Ú­È
E2=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A2)>0,VLOOKUP(Sheet1!$A2,Sheet2!$A:$C,5-COLUMN(B$1),0),Sheet1!C2)
¦V¥k¦V¤U½Æ»s
­Y­nª½±µ¨ú¥Nªí®æ¤º¸ê®Æ¶·VBA»²§U

winston219

ÁÂÁÂHsieh¤j¤j! ªº½T¥ÎVBA¸û¬°¤è«K©O~

baa168

³o­Ó¤½¦¡¬O°}¦Cªº¶Ü?
·PÁ¤À¨É®@

Andy2483

¦^´_ 1# winston219
¦^´_ 2# Hsieh


    ÁÂÁ«e½ú
¤µ¤Ñ«á¾ÇÂǦ¹¥DÃD½m²ß ¦r¨å°ò¥»¥Îªk(±a¸ê®Æªí°}¦C¦C¸¹)¨ì¥Ø¼Ðªí°}¦C,½Ð¦U¦ì«e½ú«ü±Ð

Sheet2¸ê®Æªí:

¤U¸ü (11.72 KB)

2023-3-15 08:37

Sheet1µ²ªGªí °õ¦æ«e:

¤U¸ü (33.85 KB)

2023-3-15 08:38

°õ¦æµ²ªG:

¤U¸ü (44.35 KB)

2023-3-15 08:39

Option Explicit
Sub Test()
Dim Brr, Crr, Y, i&, T$
'¡ô«Å§iÅܼÆ:(Brr,Crr,Y)¬O³q¥Î«¬ÅܼÆ,i¬Oªø¾ã¼Æ,T¬O¦r¦êÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Brr = Sheets(1).UsedRange
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C ¥H¯Á¤Þ¸¹1ªº¤u§@ªí¦³¨Ï¥ÎÀx¦s®æ­È±a¤J
Crr = Sheets(2).UsedRange
'¡ô¥OCrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C ¥H¯Á¤Þ¸¹2ªº¤u§@ªí¦³¨Ï¥ÎÀx¦s®æ­È±a¤J
For i = 2 To UBound(Crr): Y(Trim(Crr(i, 1))) = i: Next
'¡ô³]¶¶°j°é!i±q2¨ì Crr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹,
'¥Oi°j°é¦C²Ä1ÄæCrr°}¦C­È¥h°£«e«áªÅ¥Õ¦r¤¸·íkey,Item¬O °j°é¼Æi(¦C¸¹)¯Ç¤JY¦r¨å
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q2¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Brr(i, 1))
   '¡ô¥OT³o¦r¦êÅܼƬO i°j°é¦C²Ä1ÄæBrr°}¦C­È¥h°£«e«áªÅ¥Õ¦r¤¸ªº·s¦r¦ê
   If Y.Exists(T) Then
   '¡ô¦pªG¥HTÅÜ¼Æ ¬dY¦r¨å¸Ì¦³³okey ?
      Brr(i, 3) = Crr(Y(T), 3): Brr(i, 4) = Crr(Y(T), 2)
      '¡ô¥Oi°j°é¦C²Ä3ÄæBrr°}¦C­È¬O Y(T)¦C²Ä3ÄæCrr°}¦C­È
      'Y(T)¬O TÅܼƬdY¦r¨å¦^¶Çitem­È
      '¡ô¥Oi°j°é¦C²Ä4ÄæBrr°}¦C­È¬O Y(T)¦C²Ä2ÄæCrr°}¦C­È
   End If
Next
Sheets(1).[G1].Resize(UBound(Brr), 4) = Brr
'¡ô¥O¯Á¤Þ¸¹1ªº¤u§@ªí±q [G1]¶}©lÂX®i¦V¤U Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ¦C,
'¦V¥kÂX®i 4Äæ,³oÂX®i½d³òªºÀx¦s®æ¥HBrr°}¦C­È±a¤J
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub