- ©«¤l
- 967
- ¥DÃD
- 0
- ºëµØ
- 0
- ¿n¤À
- 1001
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN XP
- ³nÅ骩¥»
- OFFICE 2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-11-29
- ³Ì«áµn¿ý
- 2022-5-17
|
¦^´_ 3# toxin - Sub xx()
- '³]©w¥H¤U¦b¾ú¦~¦¨ÁZ¤u§@ªí¤§¤U¤u§@
- With Sheets("¾ú¦~¦¨ÁZ")
- '³]©w¾ú¦~¦¨ÁZ¤¤A3~AÄæ³Ì«á¤@¦C¬°Rng½d³ò
- Set Rng = .Range("A3:A" & .[A65536].End(xlUp).Row)
- '²M°£Rng½d³ò¤§©³¦â
- Rng.Interior.ColorIndex = 0
- '°j°é1:C=2~²Ä1¦C³Ì«á1Ä椧¦C¸¹(²Ä1¦C¦³Èªº½d³ò)
- For C = 2 To .[IV1].End(xlToLeft).Column
- 'Sh=B1&B2,C1&C2,D1&D2(³]©w¾Ç¦~«×¾Ç´Á¦Ò¦¸¦WºÙ)
- Sh = .Cells(1, C) & .Cells(2, C)
- '°j°é2:S=1~³Ì«á¤@±i¤u§@ªí¤§¼Æ¶q
- For S = 1 To Sheets.Count
- 'Y(¾Ç¦~«×¾Ç´Á¦Ò¦¸¦WºÙ)=(¤u§@ªí¦WºÙ)«h°õ¦æ°j°é3
- If Sh = Sheets(S).Name Then
- '°j°é3:A=A3~AÄæ³Ì«á¤@¦C¤§È
- For Each A In Rng
- '¬dA(©m¦W)¦b¬Û¹ï(¾Ç¦~«×¾Ç´Á¦Ò¦¸)¤u§@ªíA3~A65536¤§¦ì¸m,§ä¨ì«á¶Ç¦^²Ä2Ä椧È(¤À¼Æ)
- X = Application.VLookup(A.Value, Sheets(Sh).[A3:B65536], 2, 0)
- 'Y¶Ç¦^È(¤À¼Æ)¤£¬O¿ù»~«hA(©m¦W)¦V¥k°¾²¾C-1Äæ«á¶ñ¤J¤À¼Æ
- If Not IsError(X) Then A.Offset(0, C - 1).Value = X
- 'YA(©m¦W)¦bA3~AÄæ³Ì«á¤@¦C«ÂÐ¥X²{,«h¶ñ¤J¬õ¦â©³¦â
- If Application.CountIf(Rng, A) > 1 Then A.Interior.ColorIndex = 3
- Next
- End If
- Next S
- Next C
- End With
- End Sub
½Æ»s¥N½X |
|