- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 9# happycoccolin - Option Explicit
- Sub Ex()
- Dim d As Object, i As Variant, S As Variant, Wb As Workbook
- Set d = CreateObject("scripting.dictionary")
- With Application.FileDialog(msoFileDialogFilePicker) 'FileDialog :ªíÀÉ®×¹ï¸Ü¤è, msoFileDialogFilePicker(°Ñ¼Æ):¿ï¨úÀÉ®×
- .AllowMultiSelect = False '¤¹³\¨Ï¥ÎªÌ±qÀÉ®×¹ï¸Ü¤è¶ô¿ï¨ú¦hÓÀÉ®×=False
- If .Show = False Then MsgBox "¨S¦³¿ï¾ÜÀÉ®× !!!": Exit Sub
- Set Wb = Workbooks.Open(.SelectedItems(1)) '¶}±Ò«ü©wÀÉ®×
- End With
- i = 1
- With Workbooks("A.xlsx").Sheets(1) '
- Do While .Cells(i, "e") <> ""
- d(.Cells(i, "e").Value) = .Cells(i, "A").Value
- i = i + 1
- Loop
- End With
- i = 1
- With Wb.Sheets(1)
- Do While .Cells(i, "J") <> ""
- S = Join(Application.Transpose(Application.Transpose(.Range("A" & i & ":J" & i))), ",")
- If d.Exists(.Cells(i, "J").Value) Then
- S = S & "," & d(.Cells(i, "J").Value)
- d(.Cells(i, "J").Value) = Split(S, ",")
- Else
- d(.Cells(i, "J").Value) = Split(S & ",No Data", ",")
- S = d(.Cells(i, "J").Value)
-
- End If
- i = i + 1
- Loop
- .Parent.Close False 'Ãö³¬«ü©wÀɮפ£¦sÀÉ
- End With
-
- For Each i In d.keys
- If InStr(i, "-") Then If Mid(i, InStr(i, "-"), 2) <> "-1" Then d.Remove i '¥i©¿²¤"-"ªº¨BÆJ
- Next
- With Workbooks("C.xlsx").Sheets(1)
- .Cells.Clear
- S = Application.Transpose(Application.Transpose(d.ITEMS))
- .[A1].Resize(UBound(S, 1), UBound(S, 2)) = S
- End With
- End Sub
½Æ»s¥N½X |
|