ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¤£¦n·N«ä ·s¤â¤S¨Ó½Ð±Ð ¸ê®Æ¤ñ¹ï°ÝÃD ³Â·Ð¦U¦ì¤F

¦^´_ 20# ffntldj
1.   If IsError(Application.Match(Rng, Ar, 0))   ³o¥y¬O"¦b°}¦C¤ñ¹ï¤£¨ì¦P¼Ëªº¦r¦ê ¶Ç¦^¿ù»~"    ¬O»¡ ¦pªGar¸Ì­±¤£¬OMod part©MACTION´N·|¶Ç¦^­ÈµM«á©¹¤U¨«¹À?
   A:¤£¬Oªº, ¬O Rng ¤ñ¹ï  Ar °}¦C¤¤(TMD12,TDD13,MDT143,MDT14.....)  ¨ì¨S¦³­«½Æªº, µM«á¤U­±ªºµ{¦¡½X ±NRngªº­È¥[¤J Ar °}¦C¤¤

2.  If IsError(Application.Match(Rng(1) & Rng(2).Offset(, 1), Ar, 0)) Then   
     ³oÃ䪺ope_no ¬O¥ÎOffsetªº¤è¦¡¥h¼g,¤µ¤Ñ¦A·Q¤@¦U°ÝÃD ¦pªG·Q¥ÎÄæ¦ì¦WºÙ(ope_no)¥h»{ªº¸Ü³oÃä¸Ó«ç»ò§ï¼g?¬O¤£¬O¥Î¤@­Ó¦^°é¥h§ì²Ä¤@¦Cªº¦W¦r?
    A:¤£¤F¸Ñ§Aªº·N«ä
3.¦pªGA sheet¦b¤ñ¹ï B sheet®É­Ô, ¦pªG¥u­nB sheet¸ÌPART IDªº²Ä¤@½X¨ì²ÄN½X  (¦pTMD1213 ,TMD1214)·í¥Lµ¥©ó A Sheet¸Ì­±ªº­È®É ,¤]­n§â¥¦§ì¥X¨Óªº¸Ü ¸Ó«ç»ò°µ? ³o§Ú¯uªº´N¤£·|¤F~~
­×§ï¤@¤U,¬O³o¼Ë½X?
  1. Sub ¸Ñµª2Ex()
  2. Dim Rng(1 To 2) As Range, Ar, Msg As Boolean, R As Range, N As String
  3. Dim Word_In As String, Word_Out As String, Word_Look As String
  4. Word_In = "Mod part" '¶i¤J¦r¦ê
  5. Word_Out = "ACTION" 'Â÷¶}¦r¦ê
  6. Word_Look = "MODIFY"
  7. Set Rng(1) = Sheets("A").[A1] '´M§ä¦r¦êªº°_©lÂI
  8. ReDim Ar(0) '­«·s«Å§i°}¦Cªººû¼Æ
  9. Do
  10. If UCase(Rng(1)) = UCase(Word_In) Then Msg = True '¬O¶i¤J¦r¦ê ÅÞ¿è­È=True
  11. If UCase(Rng(1)) = UCase(Word_Out) Then Msg = False '¬OÂ÷¶}¦r¦ê ÅÞ¿è­È=False
  12. If Msg = True And UCase(Rng(1)) <> UCase(Word_In) Then 'ÅÞ¿è­È=True ¥B¦r¦ê¤£¬O"¶i¤J¦r¦ê"
  13. Set Rng(2) = Sheets("a").Columns(1).Find(Word_Look, After:=Rng(1), lookat:=xlWhole, MatchCase:=False) '´M§ä³Ì±µªñªº "MODIFY"
  14. If IsError(Application.Match(Rng(1) & Rng(2).Offset(, 1), Ar, 0)) Then '¤ñ¹ï¤£¨ì "PARTID&OPE_NO"¦r¦ê ¶Ç¦^¿ù»~­È
  15. If Ar(UBound(Ar)) <> "" Then ReDim Preserve Ar(UBound(Ar) + 1)
  16. Ar(UBound(Ar)) = Rng(1) & Rng(2).Offset(, 1) 'UBound(Ar) °}¦Cªº³Ì¤jºû¼Æ
  17. End If
  18. End If
  19. Set Rng(1) = Rng(1).Offset(1) '³]©w Rng(1)=Rng(1)ªº¤U¤@¦C
  20. Loop Until Rng(1) = "" 'Â÷¶} DO °j°éªº±ø¥ó¬O Until(ª½¨ì) Rng(1) = ""
  21. Set Rng(1) = Nothing 'ÄÀ©ñÅܼÆ
  22. For Each R In Sheets("B").Range("a1").CurrentRegion.Rows 'R ->¨Ì§Ç¦bSheets("B")[A1©µ¦ù½d³òªº¨C¤@¦C
  23. N = Mid(R.Cells(1), 1, 4) '¿z¿ï·Ç«h: PART IDªº²Ä¤@½X¨ì²Ä 4 ½X
  24. 'Filter(Ar, N, True) ->¶Ç¦^°}¦C ¥]§t°ò©ó«ü©w¿z¿ï·Ç«hªº¤@­Ó¦r¦ê°}¦Cªº¤l¶°
  25. If UBound(Filter(Ar, N, True)) > -1 Then '-1 °}¦C¤¤¤£¬OªÅªº:¦³¤ñ¹ï¨ì) AÄæ ªº¦r¦ê
  26. If Rng(1) Is Nothing Then Set Rng(1) = R Else Set Rng(1) = Union(Rng(1), R) '³]©wÅܼÆ
  27. End If
  28. Next
  29. With Sheets("B1")
  30. .UsedRange.Clear '²M°£ Sheets("B1")ªº¤º®e
  31. Rng(1).Copy .[A1]
  32. End With
  33. End Sub
½Æ»s¥N½X

TOP

§A¦n ÁÂÁª©¥D¸ò¶W¯Åª©¥Dªº¤j¤OÀ°¦£ ¦³³o­Óºô¯¸¹ï§Ú¨Ó»¡¹ê¦b¬OÀò¯q¨}¦h~ ÁÂÁ§A­Ì

¤p§Ìªì¾Çvba «Ü¦hªF¦è¤£·|¡@³£¬O¦b³o§V¤Oª¦¤åªº¡@«Ü·PÁ¦³³o¦a¤è

¥H¤Wªºcode¨S¿ù ¿ùªº¬O§Ú¦Û¤vªºÆ[©À ÁÂÁ§A­ÌªºªÈ¥¿

14.If IsError(Application.Match(Rng(1) & Rng(2).Offset(, 1), Ar, 0)) Then
³oÃä§ì¨ìªºrng(2)¬OMODIFY µM«á¥Îshift¥h§ìope_noªº­È,§Úªº·N«ä¬O ¦pªG¤£¥Îoffset ¦³¤°»ò¤è¦¡¥i¥H¥h»{¥LªºÄæ¦ì¦WºÙ ope_no ?

22.For Each R In Sheets("B").Range("a1").CurrentRegion.Rows 'R ->¨Ì§Ç¦bSheets("B")[A1©µ¦ù½d³òªº¨C¤@¦C

³oÀ³¸Ó¬O§âb-sheet ¤@¦C¤@¦Cªº¸ê®Æ©¹¤U§ì¥X¨Ó,¦pªG§Ú¹³¤W­±¤@¼Ë¤]¬O»Ý­n§ìÄæ¦ì¦WºÙ©O?

(´N¬O·íb-sheet part_id ¸ò ope_no ³£²Å¦X®É,¥i¥H¥h§ì¨ìflow ¸òflow1ªº¦WºÙ,µM«á§â¥¦copy¨ìb1 sheet)

¤£ª¾¹D³o¼Ëªº´y­z¬ÝªºÀ´¹À ÁÂÁª©¥D

TOP

¦^´_ 22# ffntldj
Q1: ¦pªG¤£¥Îoffset ¦³¤°»ò¤è¦¡¥i¥H¥h»{¥LªºÄæ¦ì¦WºÙ ope_no ?
A1:½Ð©¹¬Ý13¦æ:  Set Rng(2) = Sheets("a").Columns(1).Find(Word_Look, After:=Rng(1), lookat:=xlWhole, MatchCase:=False) '´M§ä³Ì±µªñªº "MODIFY"
ope_no  ¦b MODIFYªº¥kÃä¤@Äæ ·íµM¬O¥Î   Rng(2).Offset(, 1) ¨Óªí¥Ü³Ì²«K, ©Î¬O  Sheets("a").Range( "B" & Rng(2).Row)  ¤]¥i¥H

Q2:³oÀ³¸Ó¬O§âb-sheet ¤@¦C¤@¦Cªº¸ê®Æ©¹¤U§ì¥X¨Ó,¦pªG§Ú¹³¤W­±¤@¼Ë¤]¬O»Ý­n§ìÄæ¦ì¦WºÙ©O?
(´N¬O·íb-sheet part_id ¸ò ope_no ³£²Å¦X®É,¥i¥H¥h§ì¨ìflow ¸òflow1ªº¦WºÙ,µM«á§â¥¦copy¨ìb1 sheet)
A2: VBA­n§ì¸ê®Æªº»yªk¤£¤î¤@­Ó ,¦p¤W Sheets("a").Range( "B" & Rng(2).Row) §A¥iºCºCÅé·|.

TOP

¦^´_ 23# GBKEE

¤£¦n·N«ä ¬ã¨s¤F¤@­Ó§«ô ÁÙ¬O«Üµh­W «Ü¦h°ÝÃD ¦A¨Ó½Ð±Ð~ ÁÂÁÂ

1 ¦pªþÀÉ A SHEET,¤§«e³£¨S¦³ªÅ¥Õ®æ¶}(²{¦b¦³ªÅ¥Õ ©Ò¥H¤£¯à¥ÎDo ,Loop Until Rng = "",§Ú§â¥¦§ï¼g¦¨For each rng(1) in sheet("a").range(sheet(a).[A1],sheet(a)[A65536].End(xlup))
¦ý²{¦b¹J¨ì¤@­Ó°ÝÃD  ¤§«eACTION ¤U¤è¥u·|¦³¤@­ÓMODIFY, ²{¦b·|¦³«Ü¦h­Ó,¦pªG¥Î Set Rng(2) = Sheets("a").Columns(1).Find(Word_Look, After:=Rng(1), lookat:=xlWhole, MatchCase:=False)  '´M§ä³Ì±µªñªº "MODIFY"
¥u·|§ìªº¨ì²Ä¤@­Ó. ¦pªGACTION¤U¤è ¥u­n¬OMODIFY SPECªº §Ú³£­n§ì¨ì ¸Ó«ç»ò°µ,¦p¤U½d¨Ò

TMD101-E121--240.05
TMD102-E123--240.05
TMD103-E124--240.05
TMC102-E125--240.05

TMD101-E121--240.06
TMD102-E123--240.06
TMD103-E124--240.06
TMC102-E125--240.06

2  SHEET B ¬O¸ê®Æ®w¸ÌªºÀÉ®×,¥»¨Ó´N¦³ªº ,·ía sheet§ì¥X¨Ó¤§«á(¦p¤WÃD),­n¥h¤ñ¹ïb sheetªº¸ê®Æ(part_id ©Mope_noÄæ¦ì),¦pªG½T©w¸ê®Æ²Å¦X´N·|¼g¤JB1Äæ¦ì(¦pB1),¦ý¬O¦bB sheet¸Ì­±PARTID·|¦³¤@¨Ç¥u¦³«e¤»½X²Å¦Xªº¸ê®Æ(¦pºñ¦â¼Ð°O),¤]­n±N¥Lµø¬°¸ê®Æ²Å¦X ³oÃ䪺¸Ü ¤£ª¾¹D«ç»ò§ï@@?

3 B1 Sheetªº item0~item3 ¬O·ícheck¹La sheet ©Mb sheet¤§«á ¦pªG¸ê®Æ²Å¦X,­n±Nb sheet¸Ì²Å¦Xªº¸ê®Æ copy ¨ìb1,  item4 «h¬O±qaªºspec id §ì¹L¨Ó(¦pµµ¦â¼Ð°O),½Ð°Ý§Ú¸Ó«ç»ò¼g?

¥H¤W ÁÂÁ¦U¦ìªº±Ð¾É~~

ScreenHunter_06 Aug. 01 22.37.gif (69.23 KB)

ScreenHunter_06 Aug. 01 22.37.gif

ScreenHunter_07 Aug. 01 22.40.gif (46.79 KB)

ScreenHunter_07 Aug. 01 22.40.gif

Book1.rar (4.76 KB)

TOP

¦^´_ 24# ffntldj
¸Õ¸Õ¬Ý
  1. Sub Ex()
  2.     Dim f As Range, f1 As Range, Rng As Range, Ar, E As Range, S1 As Integer, S2 As Integer
  3.     Dim d1 As Object, d2 As Object
  4.     Set d1 = CreateObject("scripting.dictionary")   'Ans:2 -ªºª«¥ó
  5.     Set d2 = CreateObject("scripting.dictionary")   'Ans:3 -ªºª«¥ó
  6.     With Sheets("A")                                'Ans:1 -----
  7.         Set f = .Range("A1")                        '²Ä¤@­Ó"Mod part"
  8.         Do
  9.             Set f1 = .Columns(1).Find(What:="ACTION", MatchCase:=False, After:=f)   '±q Mod part ©¹¤U§ä"ACTION"
  10.             S1 = Application.Match("OPE_NO", f1.EntireRow, 0)                       'ACTION¦C §ä¨ì"OPE_NO"Äæ¦ì
  11.             S2 = Application.Match("SPEC ID", f1.EntireRow, 0)                      'ACTION¦C §ä¨ì"SPEC ID"Äæ¦ì
  12.             Set Rng = .Range(f.Offset(1), f1.Offset(-1))                            'Mod part - ACTION"¤§¶¡ªºÀx¦s®æ
  13.             Do
  14.                 If f1 Like "MODIFY*" Then
  15.                     For Each E In Rng
  16.                         d1(Split(E, "-")(0)) = d1(Split(E, "-")(0)) & "," & f1(1, S1).Value     'Split(E, "-")(0) «e¤»½X(KEY) ¼g¤J"OPE_NO"(ITEM)
  17.                         d2(E.Value) = f1(1, S2).Value                                           'MODIFY*(KEY) ¼g¤J"SPEC ID"(ITEM)
  18.                     Next
  19.                 End If
  20.                 Set f1 = f1.Offset(1)
  21.             Loop Until (f1 = "" And f1.End(xlDown).Row = Rows.Count) Or f1.Value = f.Value
  22.             Set f = .Columns(1).Find(What:=f, MatchCase:=False, After:=f)   '©¹¤U ´M§ä"Mod part"
  23.         Loop Until f.Address = "$A$1"                                       '¦^¨ì²Ä¤@­Ó"Mod part"®ÉÂ÷¶}°j°é
  24.     End With                                                                            'Ans:1 -----End
  25.     S1 = 0
  26.     ReDim Ar(4, S1)                  '»s©w ¼g¤JB1°}¦CªºÄæ¦ì 5Äæ(0-4)
  27.     With Sheets("B")
  28.         S2 = 2
  29.         Do
  30.             If InStr(d1(Split(.Cells(S2, 1), "-")(0)), .Cells(S2, 2)) Then
  31.             'a sheet§ì¥X¨Ó¤§«á(¦p¤WÃD),­n¥h¤ñ¹ïb sheetªº¸ê®Æ(part_id ©Mope_noÄæ¦ì),¦pªG½T©w¸ê®Æ²Å¦X´N·|¼g¤JB1Äæ¦ì
  32.                 Ar(0, UBound(Ar, 2)) = .Cells(S2, 1)            'Ans:2 -----
  33.                 Ar(1, UBound(Ar, 2)) = .Cells(S2, 2)            'Ans:2 -----
  34.                 Ar(2, UBound(Ar, 2)) = .Cells(S2, 3)            'Ans:2 -----
  35.                 Ar(3, UBound(Ar, 2)) = .Cells(S2, 4)            'Ans:2 -----
  36.                 Ar(4, UBound(Ar, 2)) = d2(.Cells(S2, 1).Value)  'Ans:3 -----
  37.                 ReDim Preserve Ar(4, UBound(Ar, 2) + 1)
  38.             End If
  39.             S2 = S2 + 1
  40.         Loop Until .Cells(S2, 1) = ""                           'ªÅ¥Õ®ÉÂ÷¶}°j°é
  41.     End With
  42.     With Sheets("B1")
  43.         .UsedRange.Offset(1).Clear
  44.         .[A2].Resize(UBound(Ar, 2), 5) = Application.Transpose(Ar)
  45.     End With
  46.     Set Rng = Nothing
  47.     Set E = Nothing
  48.     Set f = Nothing
  49.     Set f1 = Nothing
  50.     Set d1 = Nothing
  51.     Set d2 = Nothing
  52. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤Hªº§Ö¼Ö¡D¤£¬O¦]¬°¥L¾Ö¦³±o¦h¡A¦Ó¬O¦]¬°¥L­p¸û±o¤Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD