[µo°Ý] ¦p¦ó¨Ï¥ÎVBA±±¨î¶i¶¥¿z¿ï¡A±ø¥ó¤º®e¤£©T©w

kim223824

¤j¤j:
°Ý­n¦p¦ó¬D¿ï¤£­«½Æªº½s¸¹?
§Ú¸Õ¤F¦n¤[¥u¥i¥Hºâ¥XÁ`­Ó¼Æ¡A¥i¬O¤À¤£¥X³æ¿Wªº½s¸¹¡A
½Ð¤j¤j¬O§_¥i¥HÀ°¦£?¨ç¼Æ©Î¬OVBA³£¥i¥H¡C

kim223824

¤j¤j ½Ð°Ý:
¦p¦ó¥ÎVBA±±¨î¶i¶¥¿z¿ïªº±ø¥ó¦Cªº¦C¼Æ(¨C¦¸¤£©T©w,¥i¯à¦³¨â­Ó±ø¥ó©Î¤T­Ó±ø¥ó)
¸ê®Æ¨Ó·½ªº¦C¼Æ¶q¤]¤£©T©w¡A(¨C¦¸³£¤£¦P)¡A½Ð°Ý¦p¦ó±±¨î?

Hsieh

¦^´_ 1# kim223824

1. Sub ex()
   
2. Dim A As Range, B As Range
   
3. Set A = Intersect([A1:E4], Range("A1").CurrentRegion) '·Ç«h
   
4. Set B = Range([A5:E5], [A5:E5].End(xlDown)) '¸ê®Æ½d³ò
   
5. B.AdvancedFilter xlFilterInPlace, A '­ì¦a¿z¿ï
   
6. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 1# kim223824

333¤£¬OÀ³¸Ó¹ïÀ³2­Ó¶Ü?

1. Sub ex()
   
2. Set d = CreateObject("Scripting.dictionary")
   
3. For Each A In Range([A4], [A4].End(xlDown))
   
4. If A.Offset(, 3) <> "" Then
   
5.   If d(A.Value) = "" Then
   
6.      d(A.Value) = A & ":" & "," & A.Offset(, 3)
   
7.      ElseIf InStr(d(A.Value), A.Offset(, 3)) = 0 Then
   
8.      d(A.Value) = d(A.Value) & "," & A.Offset(, 3)
   
9.   End If
   
10. End If
    
11. Next
    
12. s = Split(Join(d.items, ","), ",")
    
13. [I1].Resize(, UBound(s) + 1) = s
    
14. End Sub

½Æ»s¥N½X

kim223824

¤j¤j:
333¬O¹ïÀ³¨â­Ó¡A¸ê®ÆKEYIN¿ù»~

kim223824

¦^´_ 3# Hsieh

Hsieh¤j:

·í§Úªº·Ç«h¸ò¸ê®Æ½d³ò³£¬OÅܰʪº¡A»Ý­n¦p¦ó½Õ¾ãµ{¦¡?
¬O±NE4ªº"4"Åܦ¨ÅܼƶÜ?