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

[¤À¨É] Àu¤Æ¥¨¤jªºVBA

[¤À¨É] Àu¤Æ¥¨¤jªºVBA

¥»©«³Ì«á¥Ñ play9091 ©ó 2011-7-21 09:43 ½s¿è

¤p§Ì¼g¤F¤@­ÓVBA¨Ó¸Ñ¨M¡A¨C¤Ñ­n°µ¤T¤p®É³ø§iªºÊ¨¹Ò¡K¡Kªá¤F¤G¤Ñªº®É¶¡§â¥¦§Ë§¹¤F¡A¦ý¬O¥¦«D±`ªº¥¨¤j¡A¦Ó¤p§Ì¥\¤O©|²L¡A§ä¤£¨ìÀu¤Æªº¤âÂ_¡A·Q½Ð¯q¤@¤Uª©¤Wªº¥ý¶i­ÌÀ°§U¤@¤U¡I¡I¡I
  1. Sub name_test()
  2.     Application.ScreenUpdating = False
  3.     Application.DisplayAlerts = False
  4.     T = Time
  5.     For I = 4 To 10
  6. '¥s¥XKPI³ø§i
  7.     KPI = InputBox("C#KPI³ø§i¸ôÍß(¶¶§Ç±qC4¡ãC9¡A³Ì«á¤~¬OC1)")
  8.     Workbooks.Open (KPI)
  9.     Set KPI = Nothing
  10.     KPI = ActiveWorkbook.Name
  11. '¥s¥X¨C¤éC#³ø§i
  12.     Clu = InputBox("C#³ø§i¸ôÍß(¶¶§Ç±qC4¡ãC9¡A³Ì«á¤~¬OC1)")
  13.     Workbooks.Open (Clu)
  14.     Set Clu = Nothing
  15.     Clu = ActiveWorkbook.Name
  16. '½Æ¨î¨C¤éC#³ø§iªºsheet¨ìKPI³ø§i
  17.     If I <> 10 Then
  18.     Workbooks(Clu).Sheets("sheet1").Copy After:=Workbooks(KPI).Sheets("M2000 BSC KPI Report (2)")
  19.     Sheets("sheet1").Name = "C" & I
  20.     ElseIf I = 10 Then
  21.     Workbooks(Clu).Sheets("sheet1").Copy After:=Workbooks(KPI).Sheets("sheet5")
  22.     Sheets("Sheet1 (2)").Name = "C" & I
  23.     End If
  24.     Workbooks(Clu).Close SaveChanges:=False
  25.     Set Clu = Nothing
  26. '¥s¥Xpaging³ø§i
  27.     Pag = InputBox("paging³ø§i¸ôÍß")
  28.     Workbooks.Open (Pag)
  29.     Set Pag = Nothing
  30.     Pag = ActiveWorkbook.Name
  31. '½Æ¨îpaging³ø§iªºsheet¨ìKPI³ø§i
  32.     If I <> 10 Then
  33.     Workbooks(Pag).Sheets("sheet1").Copy After:=Workbooks(KPI).Sheets("M2000 BSC KPI Report (2)")
  34.     Sheets("sheet1").Name = "paging"
  35.     ElseIf I = 10 Then
  36.     Workbooks(Pag).Sheets("sheet1").Copy After:=Workbooks(KPI).Sheets("sheet2")
  37.     Sheets("Sheet1 (2)").Name = "paging"
  38.     End If
  39.     Workbooks(Pag).Close SaveChanges:=False
  40.     Set Pag = Nothing
  41. '¶}©l¥X³ø§i
  42.     Sheets("paging").Select
  43.     Range("A11").Select
  44.     Range(Selection, Selection.End(xlToRight)).Select
  45.     Range(Selection, Selection.End(xlDown)).Select
  46.     Selection.Copy
  47.     If I <> 10 Then
  48.     Sheets("M2000 MSC Paging").Select
  49.     ElseIf I = 10 Then
  50.     Sheets("sheet5").Select
  51.     End If
  52.     Range("A2").Select
  53.     Range(Selection, Selection.End(xlToRight)).Select
  54.     Range(Selection, Selection.End(xlDown)).Select
  55.     ActiveSheet.Paste
  56. '²M°£°Å¶KÁ¡
  57. '    My.Computer.Clipboard.Clear() = True
  58.     Sheets("C" & I).Select
  59.     Range("A11").Select
  60.     Range(Selection, Selection.End(xlToRight)).Select
  61.     Range(Selection, Selection.End(xlDown)).Select
  62.     Application.CutCopyMode = False
  63.     Selection.Copy
  64.     If I <> 10 Then
  65.     Sheets("M2000 BSC KPI Report").Select
  66.     Range("A3").Select
  67.     ElseIf I = 10 Then
  68.     Sheets("sheet2").Select
  69.     Range("A2").Select
  70.     End If
  71.     Range(Selection, Selection.End(xlToRight)).Select
  72.     Range(Selection, Selection.End(xlDown)).Select
  73.     ActiveSheet.Paste
  74.     If I <> 10 Then
  75. '²M°£°Å¶KÁ¡
  76. '    My.Computer.Clipboard.Clear() = True
  77.     Sheets("C" & I).Select
  78.     Application.CutCopyMode = False
  79.     Sheets("C" & I).Range("E3").Value = "=IF(ISLOGICAL(E4)," & "" & ",A10)"
  80.     Sheets("C" & I).Range("F3").Value = "=IF(ISLOGICAL(F4)," & "" & ",A10)"
  81.     Sheets("C" & I).Range("E4").Value = "=Text(Right(A11, 8), ""hh:mm:ss"") >= ""02:00:00"""
  82.     Sheets("C" & I).Range("F4").Value = "=Text(Right(A11, 8), ""hh:mm:ss"") <= ""21:30:00"""
  83.     Range("A11").Select
  84.     Range(Selection, Selection.End(xlToRight)).Select
  85.     Range(Selection, Selection.End(xlDown)).Select
  86.     Range("A10:EQ5482").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
  87.         Range("E3:F4"), Unique:=False
  88.     Selection.Copy
  89.     Sheets("M2000 BSC KPI Report (2)").Select
  90.     Range("A3").Select
  91.     Range(Selection, Selection.End(xlToRight)).Select
  92.     Range(Selection, Selection.End(xlDown)).Select
  93.     ActiveSheet.Paste
  94.     End If
  95.     Sheets("C" & I).Delete
  96.     Sheets("paging").Delete
  97.     D = InputBox("¿é¤J¤µ¤Ñªº¤é´Á(EX¡G16)")
  98.     If I <> 10 Then
  99.     Sheets("sheet2").Select
  100.     ElseIf I = 10 Then
  101.     Sheets("BSC23-43 BTS Track").Select
  102.     End If
  103.     Cells.Replace What:=D - 1, Replacement:=D, LookAt:=xlPart, SearchOrder _
  104.         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  105.     Workbooks(KPI).Close SaveChanges:=True
  106.     Set KPI = Nothing
  107.     Next I
  108.     MsgBox ("¦@¥Î®É: " & Format(Time - T, "HH:MM:SS"))
  109. End Sub
½Æ»s¥N½X
ªì¾ÇVBA¥E¨ìtwbts

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-21 08:31 ½s¿è

¦^´_ 1# play9091
  1. KPI = InputBox("C#KPI³ø§i¸ôÍß(¶¶§Ç±qC4¡ãC9¡A³Ì«á¤~¬OC1)")
  2.     Workbooks.Open (KPI)
  3.     Set KPI = Nothing
  4.     KPI = ActiveWorkbook.Name
½Æ»s¥N½X
KPI = InputBox("C#KPI³ø§i¸ôÍß(¶¶§Ç±qC4¡ãC9¡A³Ì«á¤~¬OC1)")
    Set KPI = Workbooks.Open(KPI)
  1.     Workbooks(Clu).Sheets("sheet1").Copy After:=Workbooks(KPI).Sheets("M2000 BSC KPI Report (2)")
½Æ»s¥N½X
Clu.Sheets("sheet1").Copy After:=KP.Sheets("M2000 BSC KPI Report (2)")
  1. '¶}©l¥X³ø§i
  2. Sheets("paging").Select
  3.     Range("A11").Select
  4.     Range(Selection, Selection.End(xlToRight)).Select
  5.     Range(Selection, Selection.End(xlDown)).Select
  6.     Selection.Copy
  7.     If I <> 10 Then
  8.     Sheets("M2000 MSC Paging").Select
  9.     ElseIf I = 10 Then
  10.     Sheets("sheet5").Select
  11.     End If
  12.     Range("A2").Select
  13.     Range(Selection, Selection.End(xlToRight)).Select
  14.     Range(Selection, Selection.End(xlDown)).Select
  15.     ActiveSheet.Paste
½Æ»s¥N½X
'¶}©l¥X³ø§i
    If I <> 10 Then
    Sheets("M2000 MSC Paging").Select
    ElseIf I = 10 Then
    Sheets("sheet5").Select
    End If
Sheets("paging").Range("A11").CurrentRegion.Copy  ActiveSheet.Range("A2")
CurrentRegion  :±N¿ï¾Ü½d³ò¦Û°Ê©µ¦ù->     Range(Selection, Selection.End(xlToRight)).Select
                                                                                Range(Selection, Selection.End(xlDown)).Select
¨ä¾lÃþ¦ü¥i¦Û¦æ­×§ï

TOP

¥»©«³Ì«á¥Ñ play9091 ©ó 2011-7-21 09:53 ½s¿è

§Ú§â¥¦­×§ï¦¨¤U­±ªº¼Ë¤l¡A¤£­n¥Î¡uinputbox¡vª½±µ¦bsheet¸Ì­±§â¸ôÍߥý¼g¦n¡A³o¼Ë¤l´N¤£¥Îµ¥¡uinputbox¡v¸õ¥X¨Ó¡C
¦ý¬O¥¦½T¥X²{¤F¡u¶W¥X¯Á¤Þ½d³ò¡v¥N¸¹9ªº¿ù»~¡A§Ú¥u­n§âBOOKªºª«¥ó®³±¼´N¥¿±`¤F¡A¦ý§Ú¥²¶·­n«ü©wBOOK¤ñ¸û¤£·|¥X¿ù¡C§Ú¸Õ¤F¦n¦h¤èªk¡A¥¦´N¬O¤£µ¹°Ê§@¡A¤£ª¾¹D¬O¤£¬O§Úº|¤F¤°»ò¦a¤è¡A·Ð½Ð¥ý¶i«ü¾É¤@¤U¡I¡I¡I
  1. Sub name_test()
  2.     Application.ScreenUpdating = False
  3.     Application.DisplayAlerts = False
  4.     T = Time
  5.     For I = 4 To 10
  6. '¥s¥XKPI³ø§i
  7.     KPI = Workbooks("¸ôÍߪ©").Sheets("¤u§@ªí3").Range("D" & I).Value  '¡ö³o¸Ì¥X¿ù¡I¡I¡I
  8.     Workbooks.Open (KPI)
  9.     Set KPI = Nothing
  10.     KPI = ActiveWorkbook.Name
½Æ»s¥N½X
¥t¥~¡AªO¥D¦^½ÆªºÂ²¤Æ¤è¦¡
  1. Set KPI = Workbooks.Open(KPI)
½Æ»s¥N½X
³o¨ú¥X¨Óªº¬O¥þ³¡Ãö©óBOOKªº¤º®e¡A§Ú¥u¶·­nBOOKªºÀɦW¦Ó¤w¡I¡I¡IÀ³¸Ó¬O¥Î¤£¤W¡I¦Ó¥B§Ú¤U­±¥²¶·­n¥Î¨ìBOOKªº¦W¦r¡Aºë½T¤@ÂIÀ³¸Ó·|¤ñ¸û¦n¡I
ªì¾ÇVBA¥E¨ìtwbts

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-21 11:33 ½s¿è

¦^´_ 3# play9091
Workbooks("¸ôÍߪ©").Sheets("¤u§@ªí3").Range("D" & I).Value  '¡ö³o¸Ì¥X¿ù¡I¡I¡I
¸Õ¬Ý¬Ý   Workbooks("¸ôÍߪ©.XLS")   ³oÀÉ®×¥²»Ý¬O¤w¶}±Òªº.
§Ú¥u¶·­nBOOKªºÀɦW¦Ó¤w¡I¡I¡IÀ³¸Ó¬O¥Î¤£¤W¡I¦Ó¥B§Ú¤U­±¥²¶·­n¥Î¨ìBOOKªº¦W¦r¡Aºë½T¤@ÂIÀ³¸Ó·|¤ñ¸û¦n¡I
§Ú±N§Aªºµ{¦¡½X²¤Æ¬O°t¦X§Aªºµ{§Ç»Ý¨D
1¼Óªºµ{¦¡½X¦³
Workbooks(Clu).Close SaveChanges:=False     '³o¤£¬O¤§«e ±NÀɮ׶}±Ò «á§@Ãö³¬ªº°Ê§@¶Ü?
Workbooks(Pag).Close SaveChanges:=False

TOP

¸Õ¬Ý¬Ý   Workbooks("¸ôÍߪ©.XLS")   ³oÀɮפ£»Ý¬O¤w¶}±Òªº.\
YES¡I¡I¡I¯uªº·d©w¤F¡Aª©¤j¯u±j¡A³o©Û¥ý¾Ç°_¨Ó¡K¡K¥Î³o­Ó¤èªk¬O¤£¬O³s¨S¦³¶}ªºÀɮ׳£¥i¥H§ì¨ì¸ê®Æ¡H¡H

§Ú±N§Aªºµ{¦¡½X²¤Æ¬O°t¦X§Aªºµ{§Ç»Ý¨D
1¼Óªºµ{¦¡½X¦³
Workbooks(Clu).Close SaveChanges:=False     '³o¤£¬O¤§«e ±NÀɮ׶}±Ò «á§@Ãö³¬ªº°Ê§@¶Ü?


¥¦ªº½T¬OÃö³¬¤§«e¶}±ÒªºÀɮרS¦³¿ù¡A¦ý¬OWorkbooks(Clu)¸Ì­±ªº¡uClu¡v¥²¶·¬Oºë½TªºÀɦW¡A­n¤£µM·|Ãö¤£±¼¡K¡K

¥t¥~¡G§Ú·Q­n¥u½Æ»s¤@­Ó¡u½d³ò¡vªº¡u­È¡v¡A§ÚÁÙ·Q¤£¨ì«ç»ò°µ¡A¦]¬°¤@¦¸­n½Æ»sªº¸ê®Æ¤Ó¦h¤F(¦Ê¸U¯Åªº)¡I½Æ»sªº®É­Ô­nµ¥«Ü¤[¡K¡K¦³¨S¦³¥i¥H¡u¥u½Æ»s­È¡vªº¤èªk¡I
§Ú·Q¤F¤@¨Ç¡A¦ý³£¨S¦³¦¨¥\¡K¡K½Ð¥ý¶i­Ì«ü¾É¤@¤U¦p¦ó¥u¨ú­È¡I¡I¡I
  1.     Sheets("C" I).Select
  2.     Range("A11").Select
  3.     Range(Selection, Selection.End(xlToRight)).Select
  4.     Range(Selection, Selection.End(xlDown)).Select
  5.     set GGG.value = selection.value   '¥X¿ù
½Æ»s¥N½X
ªì¾ÇVBA¥E¨ìtwbts

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-21 11:34 ½s¿è

¦^´_ 5# play9091
¨S¦³¶}ªºÀɮ׳£¥i¥H§ì¨ì¸ê®Æ  ¤£¥i¥Hªº¤W¤@¦^ÂЦ³µ§»~  ¥H§ó¥¿ ,¥i¥Î¶×¤J¥~³¡¸ê®ÆŪ¨ú.

Set KPI = Workbooks.Open(KPI) , ±NÅܼÆKPI«ü©w¬°  Workbooks.Open(KPI) ³oª«¥ó.
KPI.Close     ³o¼Ë´NÃö±¼

À³¸Ó¬O³o¼Ë   
Set GGG = Selection       ±NÅÜ¼Æ GGG«ü©w¬° Rangeª«¥ó, Selection¬ORangeª«¥ó.

TOP

¥»©«³Ì«á¥Ñ play9091 ©ó 2011-7-21 13:29 ½s¿è

¨S¦³¶}ªºÀɮ׳£¥i¥H§ì¨ì¸ê®Æ  ¤£¥i¥Hªº¤W¤@¦^ÂЦ³µ§»~  ¥H§ó¥¿ ,¥i¥Î¶×¤J¥~³¡¸ê®ÆŪ¨ú.
OK¡A²M·¡¤F¡K¡K

Set KPI = Workbooks.Open(KPI) , ±NÅܼÆKPI«ü©w¬°  Workbooks.Open(KPI) ³oª«¥ó.
KPI.Close     ³o¼Ë´NÃö±¼

¶â¡ã¤S¾Ç¨ì¤@©Û¤F¡A¦ý¬O¥Î³o­Ó¤èªkªº¸Ü¡A§Ú¤W­±¤w¸g«ü©w¦r¦êµ¹Clu¤F¡A¨S¦³²M°£¤S«ü©w·sªº¸ê®Æµ¹¥L¡A³o¼Ë¤l¤£·|³y¦¨°O¾ÐÅé°ïÅ|¶Ü¡H·|¦Y¤ñ¸û¦h°O¾ÐÅé¡K¡K¦Ó¥B§Úµo²{
Set KPI = Workbooks.Open(KPI)
¤ñ
set KPI = nothing
KPI = ActiveWorkbook.Name
ÁÙ­nºC¡K¡K

À³¸Ó¬O³o¼Ë   
Set GGG = Selection       ±NÅÜ¼Æ GGG«ü©w¬° Rangeª«¥ó, Selection¬ORangeª«¥ó.

©Ò¥H»¡À³¸Ó§â¥¦¼g¦¨¤U­±³o­Ó¼Ë¤l¤~¨ú±o¥X¨ÓÅo¡I
Set GGG = Selection
Set BBB.Value =  GGG.Value
¤~¥i¥H¡u¥u¨ú¥X­È¡vÅo¡Iµ¥¤@¤U¸Õ¸Õ¡K¡K
­è­è¸Õ¤F¡K¡K§Ú¥¢±Ñ¤F¡K¡K

¥t¥~¡G¶i¶¥¿z¿ï¦³®É­Ô·|¥X¿ù¡A³o­n«ç»ò¼Ë¤l¥h°£¿ù©O¡H¡H
¦³¤@¤jµ§¸ê®Æ¡A§Ú­n¿z¿ï¥X0200<=X<=2130
©Ò¥H§Ú¼g¤F³o­Ó§P§O¦¡¡G
=AND(TEXT(RIGHT(A11, 8), "hh:mm:ss") >= "02:00:00",TEXT(RIGHT(A11, 8), "hh:mm:ss") <= "21:30:00")
¥¦¦³®É­Ô·|¦¨¥\¡A¦³®É­Ô·|¥¢±Ñ³oÀ³¸Ó«ç»ò°µ©O¡H¡H
ªì¾ÇVBA¥E¨ìtwbts

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-21 15:08 ½s¿è

¦^´_ 7# play9091
³o¬O¤u§@ªí¨ç¼Æ¼gªk  =AND(TEXT(RIGHT(A11, 8), "hh:mm:ss") >= "02:00:00",TEXT(RIGHT(A11, 8), "hh:mm:ss") <= "21:30:00")
§Aªº¼gªk¦³»~ §A¤£¬O¦³°Ý¹L
  1. Sub Ex()
  2.     If TimeValue(Right(Range("A11"), 8)) >= #2:00:00 AM# And TimeValue(Right(Range("A11"), 8)) <= #9:30:00 PM# Then
  3.         MsgBox "¿z¿ï®É¶¡¤º"
  4.     End If
  5. '''''''' ¤@¼Ëªº®ÄªG   '''''''''''''
  6.     If TimeValue(Right([A11], 8)) >= TimeValue("2:00:00") And TimeValue(Right([A11], 8)) <= TimeValue("9:30:00") Then
  7.         MsgBox "¿z¿ï®É¶¡¤º"
  8.     End If
  9. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ play9091 ©ó 2011-7-21 15:15 ½s¿è
¦^´_  play9091
³o¬O¤u§@ªí¨ç¼Æ¼gªk  =AND(TEXT(RIGHT(A11, 8), "hh:mm:ss") >= "02:00:00",TEXT(RIGHT(A ...
GBKEE µoªí©ó 2011-7-21 15:02


¬Ýª©¥Dªº¼gªk¬O¥u¦³¹ïA11°µ§P§O¦Ó¤w¡K¡K¦ý¬O¡K¡K§Ú­n¿z¿ïªº½d³ò¦³¤W¸U­ÓÀx¦s®æ¡K¡K¥ÎLOOPªº¸Ü·|¶]«Ü¤[§a¡I
³o¤]¬O§Ú¿ï¥Î¶i¶¥¿z¿ïªº­ì¦]¡K¡K

§Ú¤W­±»¡ªº¤£ª¾¹D¹ï¤£¹ï¡A©Î³\¬O§Ú²z¸Ñ¤£°÷¡K¡K¬O¤£¬O¥i¥HÁ|¤@­Ó¹ê¨Ò¥i¥H¿z¿ïªº¡C¤U­±¬O§Ú¼gªº¿z¿ï¤º®e¡K¡K
  1.     Sheets("C" & I).Range("E3").Value = "=IF(ISLOGICAL(E4)," & "" & ",A10)"
  2.     Sheets("C" & I).Range("E4").Value = "=AND(TEXT(RIGHT(A11, 8), ""hh:mm:ss"") >= ""02:00:00"",TEXT(RIGHT(A11, 8), ""hh:mm:ss"") <= ""21:30:00"")"
  3.     Range("A11").Select
  4.     Range(Selection, Selection.End(xlToRight)).Select
  5.     Range(Selection, Selection.End(xlDown)).Select
  6.     Range("A10:EQ5482").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
  7.         Range("E3:E4"), Unique:=False
½Æ»s¥N½X
ªì¾ÇVBA¥E¨ìtwbts

TOP

¦^´_ 9# play9091
¤W¶Ç ÀÉ®×»¡©ú¬Ý¬Ý

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD