- ©«¤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
        
|
¦^´_ 24# ffntldj
¸Õ¸Õ¬Ý- Sub Ex()
- Dim f As Range, f1 As Range, Rng As Range, Ar, E As Range, S1 As Integer, S2 As Integer
- Dim d1 As Object, d2 As Object
- Set d1 = CreateObject("scripting.dictionary") 'Ans:2 -ªºª«¥ó
- Set d2 = CreateObject("scripting.dictionary") 'Ans:3 -ªºª«¥ó
- With Sheets("A") 'Ans:1 -----
- Set f = .Range("A1") '²Ä¤@Ó"Mod part"
- Do
- Set f1 = .Columns(1).Find(What:="ACTION", MatchCase:=False, After:=f) '±q Mod part ©¹¤U§ä"ACTION"
- S1 = Application.Match("OPE_NO", f1.EntireRow, 0) 'ACTION¦C §ä¨ì"OPE_NO"Äæ¦ì
- S2 = Application.Match("SPEC ID", f1.EntireRow, 0) 'ACTION¦C §ä¨ì"SPEC ID"Äæ¦ì
- Set Rng = .Range(f.Offset(1), f1.Offset(-1)) 'Mod part - ACTION"¤§¶¡ªºÀx¦s®æ
- Do
- If f1 Like "MODIFY*" Then
- For Each E In Rng
- d1(Split(E, "-")(0)) = d1(Split(E, "-")(0)) & "," & f1(1, S1).Value 'Split(E, "-")(0) «e¤»½X(KEY) ¼g¤J"OPE_NO"(ITEM)
- d2(E.Value) = f1(1, S2).Value 'MODIFY*(KEY) ¼g¤J"SPEC ID"(ITEM)
- Next
- End If
- Set f1 = f1.Offset(1)
- Loop Until (f1 = "" And f1.End(xlDown).Row = Rows.Count) Or f1.Value = f.Value
- Set f = .Columns(1).Find(What:=f, MatchCase:=False, After:=f) '©¹¤U ´M§ä"Mod part"
- Loop Until f.Address = "$A$1" '¦^¨ì²Ä¤@Ó"Mod part"®ÉÂ÷¶}°j°é
- End With 'Ans:1 -----End
- S1 = 0
- ReDim Ar(4, S1) '»s©w ¼g¤JB1°}¦CªºÄæ¦ì 5Äæ(0-4)
- With Sheets("B")
- S2 = 2
- Do
- If InStr(d1(Split(.Cells(S2, 1), "-")(0)), .Cells(S2, 2)) Then
- 'a sheet§ì¥X¨Ó¤§«á(¦p¤WÃD),n¥h¤ñ¹ïb sheetªº¸ê®Æ(part_id ©Mope_noÄæ¦ì),¦pªG½T©w¸ê®Æ²Å¦X´N·|¼g¤JB1Äæ¦ì
- Ar(0, UBound(Ar, 2)) = .Cells(S2, 1) 'Ans:2 -----
- Ar(1, UBound(Ar, 2)) = .Cells(S2, 2) 'Ans:2 -----
- Ar(2, UBound(Ar, 2)) = .Cells(S2, 3) 'Ans:2 -----
- Ar(3, UBound(Ar, 2)) = .Cells(S2, 4) 'Ans:2 -----
- Ar(4, UBound(Ar, 2)) = d2(.Cells(S2, 1).Value) 'Ans:3 -----
- ReDim Preserve Ar(4, UBound(Ar, 2) + 1)
- End If
- S2 = S2 + 1
- Loop Until .Cells(S2, 1) = "" 'ªÅ¥Õ®ÉÂ÷¶}°j°é
- End With
- With Sheets("B1")
- .UsedRange.Offset(1).Clear
- .[A2].Resize(UBound(Ar, 2), 5) = Application.Transpose(Ar)
- End With
- Set Rng = Nothing
- Set E = Nothing
- Set f = Nothing
- Set f1 = Nothing
- Set d1 = Nothing
- Set d2 = Nothing
- End Sub
½Æ»s¥N½X |
|