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

play9091

¤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

GBKEE

¦^´_ 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¦Û¦æ­×§ï

play9091

§Ú§â¥¦­×§ï¦¨¤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

GBKEE

¦^´_ 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

play9091

¸Õ¬Ý¬Ý   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

GBKEE

¦^´_ 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ª«¥ó.

play9091

¨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

GBKEE

¦^´_ 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

play9091

¦^´_  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

GBKEE

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