[µo°Ý] Ãö©ó¨Ï¥Î¿ý»s¥¨¶°½s­×µ{¦¡½X--->"µL¸ê®Æ«h¸õ¹L"

chiang0320

±N"¾µ¥"Äæ¦ì¨Ï¥Î¿z¿ï¡A±N¸ê®Æ¶K¤W­È±a¨ì¥k°¼

¤U¸ü (52.39 KB)

2016-9-6 22:10

°ÝÃD1
¦]¬°¤­Â¾µ¥¨S¦³¤H¡A¦p¦ó§ï¦¨"µL¸ê®Æ«h¸õ¹L"

°ÝÃD2
¥Ñ©ó¬O¨Ï¥Î¿ý»s¥¨¶°ªº¼Ò¦¡¡Aµ{¦¡½X¤@ªø¦ê¡A¦p¦ó¥Î²³æ´X­Óµ{¦¡½X¹F¨ì¤@¼Ò¤@¼Ëªº®ÄªG

µL¸ê®Æ«h¸õ¹L.zip (14.34 KB)

¤U¸ü¦¸¼Æ: 18

2016-9-6 22:11

rouber590324

¦p¤U¸Õ¸Õ

Sub Âà¸m()
Sheet1.[J3:N65536].ClearContents
X = Sheet1.[B65536].End(xlUp).Row
Y1 = Sheet1.[J65536].End(xlUp).Row
Y2 = Sheet1.[K65536].End(xlUp).Row
Y3 = Sheet1.[L65536].End(xlUp).Row
Y4 = Sheet1.[M65536].End(xlUp).Row
Y5 = Sheet1.[N65536].End(xlUp).Row
For M = 3 To X
If Sheet1.Cells(M, 3) = "¤@" Then
   Sheet1.Cells(Y1 + 1, 10) = Sheet1.Cells(M, 2)
   Y1 = Y1 + 1
End If
If Sheet1.Cells(M, 3) = "¤G" Then
   Sheet1.Cells(Y2 + 1, 11) = Sheet1.Cells(M, 2)
   Y2 = Y2 + 1
End If
If Sheet1.Cells(M, 3) = "¤T" Then
   Sheet1.Cells(Y3 + 1, 12) = Sheet1.Cells(M, 2)
   Y3 = Y3 + 1
End If
If Sheet1.Cells(M, 3) = "¥|" Then
   Sheet1.Cells(Y4 + 1, 13) = Sheet1.Cells(M, 2)
   Y4 = Y4 + 1
End If
If Sheet1.Cells(M, 3) = "¤­" Then
   Sheet1.Cells(Y5 + 1, 14) = Sheet1.Cells(M, 2)
   Y5 = Y5 + 1
End If
  Next
End Sub

ikboy

¦^´_ 1# chiang0320


   
¸Õ¤U:

1. Sub ZZ()
   
2. Dim ar, rng As Range, d As Object, k, t
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Set rng = Range([j2], [j2].End(2))
   
5. ar = [b2].CurrentRegion
   
6. t = [c2]
   
7. For i = 2 To UBound(ar)
   
8.     d(ar(i, 2) & t) = d(ar(i, 2) & t) & "|" & ar(i, 1)
   
9. Next
   
10. k = d.keys
    
11. For i = 0 To UBound(k)
    
12.     j = Application.Match(k(i), rng, 0)
    
13.     t = Split(Mid(d(k(i)), 2), "|")
    
14.     rng.Cells(1, j).Offset(1, 0).Resize(UBound(t) + 1, 1) = Application.Transpose(t)
    
15. Next
    
16. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

Sub TEST()
Dim xR As Range, xF As Range
For Each xR In Range([B3], [B65536].End(xlUp))
¡@¡@Set xF = [J2:N2].Find(xR(1, 2), Lookat:=xlPart)
¡@¡@If Not xF Is Nothing Then xF(60000, 1).End(xlUp)(2, 1) = xR
Next
End Sub

chiang0320

¦pªG¬O¥HÀɮ׸̭±¿ý»sªºµ{¦¡½X¥h§ï¡A­n«ç»ò§e²{©O?

GBKEE

¦^´_ 5# chiang0320
§A©Ò¿ý»s¥¨¶°µ{¦¡½X,¿@ÁY«áªºµ{¦¡½X

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim AR(), i As Integer
   
4.     AR = Array("¤@", "¤G", "¤T", "¥|", "¤­")
   
5.     Range("J2").CurrentRegion.Offset(1) = ""  '²M°£Â¦³¸ê®Æ
   
6.     For i = 0 To UBound(AR)
   
7.         ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=AR(i)    '½d³ò ¦Û°Ê¿z¿ï  «ü©w²Ä2­ÓÄæ¦ì,±ø¥ó=AR(i)
   
8.         If Range("B2").End(xlDown).Row <> Rows.Count Then  '¦³¸ê®Æ
   
9.             Range("B3", Range("B3").End(xlDown)).Copy Range("J2").Offset(1, i)
   
10.         End If
    
11.     Next
    
12.     ActiveSheet.Range("A1").AutoFilter  '¨ú®ø¦Û°Ê¿z¿ï(¨S¦³«ü©w±ø¥ó)
    
13. End Sub

½Æ»s¥N½X

chiang0320

¦^´_ 6# GBKEE


    ÁÂÁÂ! GBKEE