198188

¦^´_ 10# Andy2483


xA.Offset(1, 12).ClearContents:  Brr = xA
'¡ô¥OxAÀx¦s®æ°¾²¾¤U1¦C¥k12Ä檺°Ï°ìÀx¦s®æ¤º®e²M°£,¥OBrrÅܼƬO¥HxAÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C

³o¥y²M°£内®e¡A¥¦¬O±q²Ä13Äæ(ÄæM) ¨ì ²Ä19Äæ (ÄæS) ªº内®e²M°£¡A§Ú»Ý­nªº¥u¬O ²Ä13ÄæÄæM - ²Ä15ÄæÄæO, §Ú»Ý­n«ç¼Ë­×¥¿¡A¤~¯àÅý¥L¤£§âÄæP - ÄæSªº¸ê®Æ²M°£¡H

Andy2483

¦^´_ 11# 198188

Intersect([1!M:O], xA.Offset(1, 12)).ClearContents
'¡ô¥OxAÀx¦s®æ°¾²¾¤U1¦C¥k12Äæ½d³ò»P M:OÄæ¥æ¶°ªº°Ï°ìÀx¦s®æ¤º®e²M°£

198188

¦^´_ 12# Andy2483


    ¥i¥H¤F¡AÁÂÁ¡I

Set xA = Range([1!G1], [1!A65536].End(3))
Intersect([1!M:O], xA.Offset(1, 12)).ClearContents: Brr = xA
Arr = [1!M1].Resize(UBound(Brr), 3)
[1!M1].Resize(UBound(Brr), 3) = Arr
¥t¥~¦pªG§Ú°õ¦æªº¤u§@ªí¤£©T©w¦WºÙ¡A«ç¼Ë­×§ï¬°¥Îactivesheet?

shuo1125

¦^´_ 12# Andy2483
·PÁÂAndy¤j¼ö±¡¥B±M·~~
¤]´£¨Ñ­ÓxA.Offset(1, 12).Resize(, 3).ClearContents

Andy2483

¦^´_ 13# 198188

1.Set xA = Range([1!G1], [1!A65536].End(3))  §ï¬°
Set xA = Range(ActiveSheet.[G1], ActiveSheet.[A65536].End(3))

2.¥N½X¸Ì­±¦³[1!   ªº³£­n§ï

198188

¦^´_ 15# Andy2483


    ©ú¥Õ¡AÁÂÁÂ

Andy2483

¦^´_ 14# shuo1125

ÁÂÁ«ü±Ð,³o¤èªk¨S¥Î¹L,¤S¾Ç¨ì¤@©Û,¤§«e³£¥u·|¥Îintersect

198188

¦^´_ 17# Andy2483

­è­è§Ú´ú¸Õ¤F´X¦¸¡A¤£ª¾¹D¬O«ç麽¦^¨Æ¡A¦³ªº¼Æ¾ÚŪ¤£¥X¨Ó¡C¥i§_À°§Ú¬Ý¬Ýªþ¥ó¡A¬O§_®æ¦¡¸òVBA ¦³½Ä¬ð¡H

Andy2483

¦^´_ 18# 198188

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, i&, T$, TT$, A, xA As Range, N%
Set Z = CreateObject("Scripting.Dictionary")
Set xA = Range(ActiveSheet.[G1], ActiveSheet.[A65536].End(3))
xA.Offset(1, 12).Resize(, 3).ClearContents:  Brr = xA
For i = 2 To UBound(Brr)
   T = Format(Trim(Brr(i, 2)), "0000000"): V = Format(Val(Brr(i, 7)), "0000000"): TT = T & "/" & V
   Z(TT) = i
Next
Arr = ActiveSheet.[M1].Resize(UBound(Brr), 3)
A = Array(Range([KH!C1], [KH!A65536].End(3)), Range([KH!J1], [KH!H65536].End(3)), Range([KP!C1], [KP!A65536].End(3)), Range([KP!J1], [KP!H65536].End(3)))
For Each Crr In A
   Crr = Crr: N = N + 1
   For i = 3 To UBound(Crr)
      T = Format(Trim(Crr(i, 1)), "0000000"): V = Format(Val(Crr(i, 2)), "0000000"): TT = T & "/" & V
      If Z.Exists(TT) Then
         If Arr(Z(TT), 1) = "" Then
            Arr(Z(TT), 1) = Crr(1, 1)
            ElseIf InStr("/" & Arr(Z(TT), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
            Arr(Z(TT), 1) = Arr(Z(TT), 1) & "/" & Crr(1, 1)
         End If
         Arr(Z(TT), N \ 3 + 2) = Crr(1, 3)
      End If
   Next
Next
ActiveSheet.[M1].Resize(UBound(Brr), 3) = Arr
End Sub

198188

¦^´_ 19# Andy2483


    ¤£¦n·N«ä¡A§Ú¤W¸ü¿ù¤F¤å¥ó¡A³o­Ó¤~¹ï¡C
³¡¤À¦a¤èµLªkŪ¨ú¸ê®Æ¡C