[µo°Ý] VBA¤º§â", " Âର´«Äæ

kan109

1. Sub test()
   
2. Dim dic As Object, data, i%
   
3.     Set dic = CreateObject("scripting.dictionary")
   
4.     data = Range(Cells(1, 1), Cells(1, 2).End(4))
   
5.     For i = 1 To UBound(data)
   
6.         If Not dic.exists(data(i, 1)) Then
   
7.             dic.Add data(i, 1), data(i, 2)
   
8.         Else
   
9.         If InStr(dic(data(i, 1)), data(i, 2)) = 0 Then
   
10.             dic(data(i, 1)) = dic(data(i, 1)) & " , " & data(i, 2)
    
11.         End If
    
12.         End If
    
13.     Next
    
14.     Cells(1, 3).Resize(dic.Count, 1) = Application.Transpose(dic.keys)
    
15.     Cells(1, 4).Resize(dic.Count, 1) = Application.Transpose(dic.items)
    
16.     Columns(4).TextToColumns Destination:=Cells(1, 4), OtherChar:=" , "
    
17.    Cells.Columns.AutoFit
    
18.     Set dic = Nothing
    
19. End Sub

½Æ»s¥N½X

¥H¤W¦¹¥N½X,¦p¦ó§âµ²ªG±o¨Ó¬O¥H¨C­ÓÄæ¥N´À" , "

¤U¸ü (581.79 KB)

2018-8-31 01:06

¥H²{®É±o¥Xµ²ªG,¤Q¤À²V¶Ã
ÁÂÁÂÀ°¦£

kan109

¥t¥~,½Ð°Ý¦p¦ó¥i¥H§â¥H¤WVBA ¥Î¦bmac excel ¤W?

kan109

¥t¥~,½Ð°Ý¦p¦ó¥i¥H§â¥H¤WVBA ¥Î¦bmac excel ¤W?

n7822123

¦^´_ 1# kan109


¨S¦³ÀɮרSªkÀ°§A§ï¡A¤j·§»¡¤@¤U¡A
¥Îsplit¥i¥H§âªø¦r¦ê¨Ì","¤À¶}¦¨°}¦C
½d¨Ò¾×¦p¤U¡A¥i¥H§â²Ä¤@Äæ¨Ì³rÂI¤À¶}¦¨°}¦C¡A¦A±q²Ä2Äæ¶}©l¶ñ

aaa.rar (7.41 KB)

¤U¸ü¦¸¼Æ: 3

2018-8-31 02:52

1. Sub AA()
   
2. Dim bb() As String
   
3. For i = 1 To Cells(Rows.Count, 1).End(3).Row
   
4.   bb = Split(Cells(i, 1), ",")
   
5.   Cells(i, 2).Resize(1, UBound(bb) + 1) = bb
   
6. Next i
   
7. End Sub

½Æ»s¥N½X

kan109

¤w¤W¸ü,,ÁÂÁÂ

kan109

¦^´_ 4# n7822123

¤w¸Õ¹L,¦ý¦n¹³¸ò§Ú·Qªº¤£¤@¼Ë.ÁÂÁÂÀ°¦£

n7822123

¦^´_ 6# kan109


  ½Ð§A²M·¡±Ô­z¤@¤U§A­nªº¥Øªº¡A¤£µM½Ö¤]µLªkÀ°§A

¦pªG§A©Ò»¡ªº"±o¨Óªºµ²ªG" ¬O²Ä4Ä檺¸Ü¡A¸Õ¸Õ¤U­±³o¦êµ{¦¡
§â²Ä4Äæ¨Ì","¤ÀÄæ¡A±q²Ä5Äæ¶ñ¤W

1. Sub ddd()
   
2. Dim bb() As String
   
3. For i = 1 To  Cells(Rows.Count, 4).End(3).Row
   
4.   bb = Split(Cells(i, 4), ",")
   
5.   Cells(i, 5).Resize(1, UBound(bb) + 1) = bb
   
6. Next i
   
7. End Sub

½Æ»s¥N½X

¦pªG§A·Q­n§O¤HÀ°§A§ïµ{¦¡¡A½Ð²M·¡»¡©ú¨º¦êµ{¦¡ªº¥Øªº

n7822123

¦^´_ 7# n7822123

Á`ºâ¬ÝÀ´§A­nªº¤F¡A¬Ý¬Ý³o­Ó¬O§_²Å¦X§Aªº»Ý¨D

°²³]²Ä¤@Äæ¬O¼ÐÃD¡A²Ä¤GÄæ¬O¤º®e¡A

²Ä¤TÄæ©ñ¤£­«½Æªº¼ÐÃD¡A²Ä4Ä橹«á©ñ¦P¼Ë¼ÐÃDªº¤º®e(¤º®e¥ÎÄæ°µ¤À®æ)

À³¸Ó¬O³o¼Ë§a!?

¥þµ{§Ç­×§ï¦p¤U!

sample_001-0831.rar (289.56 KB)

¤U¸ü¦¸¼Æ: 2

2018-8-31 23:50

1. Sub test()
   
2. Dim dic As Object, data, i%, AA$
   
3. Dim bb() As String
   
4. Set dic = CreateObject("scripting.dictionary")
   
5. data = Range(Cells(1, 1), Cells(1, 2).End(4))  
   
6. For i = 1 To UBound(data)
   
7.   AA = data(i, 1)
   
8.   If Not dic.exists(AA) Then
   
9.       dic(AA) = data(i, 2)
   
10.   Else
    
11.     If InStr(dic(AA), data(i, 2)) = 0 Then
    
12.       dic(AA) = dic(AA) & "," & data(i, 2)
    
13.     End If
    
14.   End If
    
15. Next
    
16. Cells(1, 3).Resize(dic.Count, 1) = Application.Transpose(dic.keys)
    
17. For i = 1 To dic.Count
    
18.   AA = data(i, 1)
    
19.   bb = Split(dic(AA), ",")
    
20.   Cells(i, 4).Resize(1, UBound(bb) + 1) = bb
    
21. Next i
    
22. Set dic = Nothing
    
23. End Sub

½Æ»s¥N½X

n7822123

¦^´_ 8# n7822123


    ¤W¤@½g¦³ÂI¤p¿ù¡A­×§ï¦p¤U

sample_001-0831+.rar (277.19 KB)

¤U¸ü¦¸¼Æ: 4

2018-9-1 00:18

Sub test()
Dim dic As Object, data, i%, AA$
Dim bb() As String

Set dic = CreateObject("scripting.dictionary")
data = Range(Cells(1, 1), Cells(1, 2).End(4))
For i = 1 To UBound(data)
  AA = data(i, 1)
  If Not dic.exists(AA) Then
      dic(AA) = data(i, 2)
  Else
    If InStr(dic(AA), data(i, 2)) = 0 Then
      dic(AA) = dic(AA) & "," & data(i, 2)
    End If
  End If
Next
Cells(1, 3).Resize(dic.Count, 1) = Application.Transpose(dic.keys)
On Error Resume Next
For i = 1 To dic.Count
  AA = Cells(i, 3).Value
  bb = Split(dic(AA), ",")
  Cells(i, 4).Resize(1, UBound(bb) + 1) = bb
Next i
Set dic = Nothing
End Sub

kan109

¦^´_  n7822123


    ¤W¤@½g¦³ÂI¤p¿ù¡A­×§ï¦p¤U



Sub test()
Dim dic As Object, data, i%, AA ...
n7822123 µoªí©ó 2018-9-1 00:19

    ¶W·PÁÂ~~