¤£¦n·N«ä ·s¤â¤S¨Ó½Ð±Ð ¸ê®Æ¤ñ¹ï°ÝÃD ³Â·Ð¦U¦ì¤F
- ©«¤l
- 26
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 33
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-19
- ³Ì«áµn¿ý
- 2013-5-13
|
to ¶W¯Åª©¥D ¤U±ªº¨ºÓ¥\¯à§Ú¼g¥X¨Ó¤F!! ·PÁ ¨ºÓmystr¤£¥Î©w¸q¨ºÓ°ÝÃD¦b³Â·Ð§A±Ð§Ú¤@¤U ÁÂÁ |
|
|
|
|
|
|
- ©«¤l
- 26
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 33
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-19
- ³Ì«áµn¿ý
- 2013-5-13
|
¤F¸Ñ ÁÂÁ§A ¥Ø«e¤w¥Î³oÓ¬[ºc¥h«Ø¥ß§Ú·Qnªº¥\¯à
¥t¥~¸ò§A½Ð°Ý¤@Ó°ÝÃD ¦pªG§Ú·Qn§â¿é¤J§ï¦¨¥i¥H¿ï¾ÜÀÉ®×
¬O¤£¬O¥Îapplication.getopenfilename
¸Õ¤F¤@¤U ¦b With .Sheets("b") ·|¦³°ÝÃD
·P¿E¤£ºÉ |
|
|
|
|
|
|
- ©«¤l
- 26
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 33
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-19
- ³Ì«áµn¿ý
- 2013-5-13
|
¦^´_ 13# ffntldj
©êºp¨S»¡²M·¡ À³¬O¥H¤U³o¬q §ï¦¨¿ï¾ÜÀÉ®×
With Workbooks.Open '("D:\¤uµ{®v°e¥ó½d¨Ò.xls") 'Àɮ׸ô®|¦Û¦æקï |
|
|
|
|
|
|
- ©«¤l
- 26
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 33
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-19
- ³Ì«áµn¿ý
- 2013-5-13
|
¥»©«³Ì«á¥Ñ ffntldj ©ó 2011-7-22 23:18 ½s¿è
¦^´_ 15# Hsieh
§Ú¼g¥X¨Ó¤F~~~ ÁÂÁ µ{¦¡½Xµy«áªþ¤W
¦p¤U
myfile = workbook.application.getopenfilename ("excel¤å¥ó(*.xls),*.xls")
with workbooks.open(myfile)
´N¥i¥H¥Î·Æ¹«ÂIÀɮפF
·PÁª©¥D~ |
|
|
|
|
|
|
- ©«¤l
- 26
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 33
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-19
- ³Ì«áµn¿ý
- 2013-5-13
|
¤£¦n·N«ä ·s¤â¤S¨Ó½Ð±Ð ¸ê®Æ¤ñ¹ï°ÝÃD ³Â·Ð¦U¦ì¤F
¥»©«³Ì«á¥Ñ ffntldj ©ó 2011-7-23 01:26 ½s¿è
¤£¦n·N«ä ¹ê¦b¼g¤£¥X¨Ó ¥Î¤F¤@°ïif ¸òfor ¹ê¦b¦nÃø ¦³°ÝÃD·Q½Ð±Ð,
¸ê®Æ¤£¥u¤Tµ§ ¥u¦C¥X¤Tµ§ (ªþ¹Ï¦p¤U)
1.¦pªG·Q§â A sheet¸Ì mod part¥H¤U action¥H¤W (¬õ¦â¦rÅé )¸Ìªº¸ê®Æ ¶ñ¤JA1 ¦ý¬O¦pªG¸ê®Æ¬O«ÂÐ ,«h¤£«½Æªí¥Ü (¦pA1 sheet) ¸Ó«ç»ò°µ?
¤§«e¬O¥ÎªO¤j±Ðªº
Set d = CreateObject("Scripting.Dictionary")
If a = "Mod part" Then mystr = a.Offset(1,0).Text
¦ý¬O¦pªG¹J¨ìMod part¤U±¦³«Ü¦hµ§ ´N¤£ª¾¹D¸Ó«ç»ò¼g¤F
2. ¤ñ¹ïA sheet ©M C sheet¸Ì±ªº¸ê®Æ,·íA sheet¸Ìªº¬õ¦â¸òÂŦâ , ¸òB sheetªºpartidÄæ¦ì¸òope_noÄæ¦ì
³£¯à°÷matchªº¸Ü,±N¹ïÀ³¨ìªº¸ê®Æ©ñ¨ì B1 sheet¸Ì± (¦pB1©Ò¥Ü)
ªþ¥ó¦p¤U ¦A³Â·Ð¤j®a¤F ¯u¬O¤£¦n·N«ä
Book1.rar (2.57 KB)
|
|
|
|
|
|
|
- ©«¤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
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-23 09:23 ½s¿è
¦^´_ 1# ffntldj
¤ñ¹ïA sheet ©M C sheet¸Ì±ªº¸ê®Æ ªþÄÒ¤¤¨S¦³C sheet ¥i¥H§ó·s¶Ü?- Sub ¸Ñµª1Ex()
- Dim Rng As Range, Ar, Msg As Boolean
- Dim Word_In As String, Word_Out As String
- Word_In = "Mod part" '¶i¤J¦r¦ê
- Word_Out = "ACTION" 'Â÷¶}¦r¦ê
- Set Rng = Sheets("A").[A1] '´M§ä¦r¦êªº°_©lÂI
- ReDim Ar(0) '«·s«Å§i°}¦Cªººû¼Æ
- Do
- If Rng = Word_In Then Msg = True '¬O¶i¤J¦r¦ê ÅÞ¿èÈ=True
- If Rng = Word_Out Then Msg = False '¬OÂ÷¶}¦r¦ê ÅÞ¿èÈ=False
- If Msg = True And Rng <> Word_In Then 'ÅÞ¿èÈ=True ¥B¦r¦ê¤£¬O"¶i¤J¦r¦ê"
- ' Application.Match(Rng, Ar, 0) '¦b°}¦C¤ñ¹ï¤£¨ì¦P¼Ëªº¦r¦ê ¶Ç¦^¿ù»~È
- If IsError(Application.Match(Rng, Ar, 0)) Then '¶Ç¦^¿ù»~È
- If Ar(UBound(Ar)) <> "" Then ReDim Preserve Ar(UBound(Ar) + 1)
- 'Preserve «O¯d°}¦C즳¸ê®ÆªºÃöÁä¦r
- Ar(UBound(Ar)) = Rng 'UBound(Ar) °}¦Cªº³Ì¤jºû¼Æ
- End If
- End If
- Set Rng = Rng.Offset(1) '³]©w Rng=Rngªº¤U¤@¦C
- Loop Until Rng = "" 'Â÷¶} DO °j°éªº±ø¥ó¬O Until(ª½¨ì) Rng = ""
- Sheets("A1").Rows(1) = "" '¾ã¦C
- Sheets("A1").[A1].Resize(1, UBound(Ar) + 1) = Ar 'Resize Àx¦s®æÂX¥R½d³ò(1¦C, Äæ¦ì:=UBound(Ar) + 1)
- End Sub
½Æ»s¥N½X- Sub ¸Ñµª2Ex()
- Dim Rng(1 To 2) As Range, Ar, Msg As Boolean, R As Range
- Dim Word_In As String, Word_Out As String, Word_Look As String
- Word_In = "Mod part" '¶i¤J¦r¦ê
- Word_Out = "ACTION" 'Â÷¶}¦r¦ê
- Word_Look = "MODIFY"
- Set Rng(1) = Sheets("A").[A1] '´M§ä¦r¦êªº°_©lÂI
- ReDim Ar(0) '«·s«Å§i°}¦Cªººû¼Æ
- Do
- If UCase(Rng(1)) = UCase(Word_In) Then Msg = True '¬O¶i¤J¦r¦ê ÅÞ¿èÈ=True
- If UCase(Rng(1)) = UCase(Word_Out) Then Msg = False '¬OÂ÷¶}¦r¦ê ÅÞ¿èÈ=False
- If Msg = True And UCase(Rng(1)) <> UCase(Word_In) Then 'ÅÞ¿èÈ=True ¥B¦r¦ê¤£¬O"¶i¤J¦r¦ê"
- Set Rng(2) = Sheets("a").Columns(1).Find(Word_Look, After:=Rng(1), lookat:=xlWhole, MatchCase:=False) '´M§ä³Ì±µªñªº "MODIFY"
- If IsError(Application.Match(Rng(1) & Rng(2).Offset(, 1), Ar, 0)) Then '¤ñ¹ï¤£¨ì "PARTID&OPE_NO"¦r¦ê ¶Ç¦^¿ù»~È
- If Ar(UBound(Ar)) <> "" Then ReDim Preserve Ar(UBound(Ar) + 1)
- Ar(UBound(Ar)) = Rng(1) & Rng(2).Offset(, 1) 'UBound(Ar) °}¦Cªº³Ì¤jºû¼Æ
- End If
- End If
- Set Rng(1) = Rng(1).Offset(1) '³]©w Rng(1)=Rng(1)ªº¤U¤@¦C
- Loop Until Rng(1) = "" 'Â÷¶} DO °j°éªº±ø¥ó¬O Until(ª½¨ì) Rng(1) = ""
- Set Rng(1) = Nothing 'ÄÀ©ñÅܼÆ
- For Each R In Sheets("B").Range("a1").CurrentRegion.Rows 'R ->¨Ì§Ç¦bSheets("B")[A1©µ¦ù½d³òªº¨C¤@¦C
- If Not IsError(Application.Match(R.Cells(1) & R.Cells(2), Ar, 0)) Then '°}¦C¤¤¤ñ¹ï¨ìSHEETS("B") AÄæ&BÄæ ªº¦r¦ê
- If Rng(1) Is Nothing Then Set Rng(1) = R Else Set Rng(1) = Union(Rng(1), R) '³]©wÅܼÆ
- End If
- Next
- With Sheets("B1")
- .UsedRange.Clear '²M°£ Sheets("B1")ªº¤º®e
- Rng(1).Copy .[A1]
- End With
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 26
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 33
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-19
- ³Ì«áµn¿ý
- 2013-5-13
|
¤ñ¹ïA sheet ©M C sheet¸Ì±ªº¸ê®Æ,·íA sheet¸Ìªº¬õ¦â¸òÂŦâ , ¸òB sheetªºpartidÄæ¦ì¸òope_noÄæ¦ì
¦¹³B¥´¿ù À³¸Ó¬O ¤ñ¹ïA sheet ©M B sheet ,·íA sheet¸Ìªº¬õ¦â¸òÂŦâ , ¸òB sheetªºpartidÄæ¦ì¸òope_noÄæ¦ì
©êºp µ{¦¡½X§Ú¬ã¨s¤@¤U |
|
|
|
|
|
|
- ©«¤l
- 26
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 33
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-19
- ³Ì«áµn¿ý
- 2013-5-13
|
ª©¥D§A¦n
ÁÂÁ§Aªºcode Åý§ÚÀò¯q¨}¦h «Ü·PÁ§A code¨S¦³°ÝÃD ,¦ý¦³¨Ç¦a¤è§Ú¬Ý¤£À´ google¹L¤F¤]ÁÙ¬O¤£·|
·Qª½±µ¸ò§A½Ð±Ð,ÁÂÁÂ
1. If IsError(Application.Match(Rng, Ar, 0)) ³o¥y¬O"¦b°}¦C¤ñ¹ï¤£¨ì¦P¼Ëªº¦r¦ê ¶Ç¦^¿ù»~"
¬O»¡ ¦pªGar¸Ì±¤£¬OMod part©MACTION´N·|¶Ç¦^ȵM«á©¹¤U¨«¹À?
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?
3.¦pªGA sheet¦b¤ñ¹ï B sheet®ÉÔ, ¦pªG¥unB sheet¸ÌPART IDªº²Ä¤@½X¨ì²ÄN½X (¦pTMD1213 ,TMD1214)·í¥Lµ¥©ó A Sheet¸Ì±ªºÈ®É ,¤]n§â¥¦§ì¥X¨Óªº¸Ü ¸Ó«ç»ò°µ? ³o§Ú¯uªº´N¤£·|¤F~~
¥H¤W ÁÂÁÂ |
|
|
|
|
|
|