- ©«¤l
- 1447
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1471
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-12-5
|
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub TEST() '¡ô
Dim Brr, Crr, Y, i&, j&, T$, K%
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = [A2:E5]
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HÀx¦s®æȱa¤J°}¦C¤¤
K = UBound(Brr)
'¡ô¥OKÅܼƬOBrr°}¦C³Ì¤j¯Á¤Þ¦C¸¹
For i = 1 To K
For j = 2 To UBound(Brr, 2)
T = Brr(i, 1) & "/" & Brr(i, j)
Y(T) = Brr(K, j)
Next
Next
'¡ô³]¶¶°j°é±N¸ê®Æ°Ï²Õ¦X¦r¦ê·íkey,item¬OÃþ§O,¯Ç¤JY¦r¨å¤¤
Brr = Range([C24], [B65536].End(xlUp))
'¡ô¥OBrrÅܼƫ·s©w¸q¬O ¤Gºû°}¦C,´«¸Ë·sÀx¦s®æÈ
ReDim Crr(1 To UBound(Brr), 1 To 1)
'¡ô«Å§iCrr°}¦C¬O ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr,¾î¦V½d³ò1~1¯Á¤Þ¸¹
For i = 1 To UBound(Brr)
T = Brr(i, 1) & "/" & Brr(i, 2)
If Y(T) = "" Then
Crr(i, 1) = "EÃþ"
Else
Crr(i, 1) = Y(T)
End If
Next
'¡ô³]¶¶°j°é±N¥Ø¼Ð°Ï²Õ¦X¦r¦ê¬dY¦r¨å±o¨ìitemȱa¤JCrr°}¦C¤¤,
'¦pªG¬dY¦r¨å¨äitemȬO"",Crr°}¦C«h¼g¤J"EÃþ"
[E24].Resize(UBound(Crr)) = Crr
'¡ô¥OCrr°}¦Cȼg¦pµ²ªGÀx¦s®æ¤¤
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|