- ©«¤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
|
¦^´_ 8# j88141
¨âÀɮתº"¤¤¤È" ³o¦r¦ên¤@P- Option Explicit
- Dim D As Object
- Private Sub Ex() 'ExcelÀÉ®×(ÀÉ®×2),¥[¤J¤@ÓCommandButton ªºµ{¦¡½X
- Dim SH As Worksheet, Rng As Range, i As Integer
- Dictionary_Ex '°õ¦æ³oÓµ{§Ç
- For Each SH In Sheets 'Sheets :ExcelÀÉ®×(ÀÉ®×2)¤¤ªº¤u§@ªí¶°¦X
- Set Rng = SH.[A3] '½s¸¹
- Do While Rng <> ""
- For i = 4 To SH.UsedRange.Columns.Count
- If D.exists(SH.Cells(2, i) & Rng & SH.[A1]) Then '¦r¨åª«¥ó¤¤¦³³o key È
- 'key È-> ¬P´Á & ½s¸¹ & ¦a¦W
- '¬P´Á: Sh.Cells(2,i)
- '½s¸¹: Rng
- '¦a¦W" SH.[A1]
- D(SH.Cells(2, i) & Rng & SH.[A1]).Copy Rng.Cells(1, i).Resize(4)
- Else
- Rng.Cells(1, i).Resize(4) = ""
- End If
- Next
- Set Rng = Rng.End(xlDown) '¤U¤@Ó¬P´Áªº¦ì¸m
- Loop
- Next
- End Sub
- Private Sub Dictionary_Ex()
- Dim Rng(1 To 3) As Range, i As Integer, a
- Set D = CreateObject("SCRIPTING.DICTIONARY")
- With Workbooks("Xl0000001.xls").Sheets("¤u§@ªí1") 'ì©l¸ê®ÆÀÉ®×¥²¶·¬O¶}±Òªº
- Set Rng(1) = .[A4] '¬P´Á
- Do While Rng(1) <> ""
- Set Rng(2) = Rng(1).Offset(, 1) '½s¸¹
- Do While Not Intersect(Rng(1).MergeArea, Rng(2).Offset(, -1)) Is Nothing
- For i = 4 To .UsedRange.Columns.Count - 1
- If Rng(2).Cells(1, i) <> "" Then '
- Set D(Rng(1) & Rng(2) & Rng(2).Cells(3, i)) = Rng(2).Cells(1, i).Resize(4)
- '¦a¦W: Rng(2).Cells(3, i)
- End If
- Next
- Set Rng(2) = Rng(2).End(xlDown) '¤U¤@Ó½s¸¹ªº¦ì¸m
- Loop
- Set Rng(1) = Rng(1).End(xlDown) '¤U¤@Ó¬P´Áªº¦ì¸m
- Loop
- End With
- End Sub
½Æ»s¥N½X |
|