[¤À¨É] À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

play9091

¦^´_ 14# GBKEE

·PÁª©¤j­@¤ßªº¦^µª§Úªº¨C¤@­ÓÄê°ÝÃD¡K¡KÃö©ó¶i¶¥¿z¿ïªº°ÝÃD¡A§Ú§ä¨ì¿ù»~ªº¦a¤è¤F¡A´N¬O§Ú¿ï¾Ü³Q¿z¿ïªº¦a¤è¿ïªº¤£°÷¤j¡K¡K©Ò¥H¦³¨Çªº¸ê®Æ¨S¦³³Q¿z¿ï¨ì¡C
©ú¤Ñ§Ú¤S­n¶}©l§Úªº¥t¥~¤@¥÷VBA®Èµ{¡AÁٽХý¶i­Ì¦h¦h«ü±Ð¡K¡K
§Ú¤]·|«ùÄòªº¥´ÂZ¦U¦ìªº¡I¡I¡I

GBKEE

¦^´_ 13# play9091
¥i¥H§r,µ{¦¡½X¸ò¤W¤@ÀÉ®×®t¤£¦h,§ä¤£¨ì¿ù»~.

1. Sub ttt()
   
2.     Sheets("Sheet1").Range("E3").Value = ""
   
3.     Sheets("Sheet1").Range("E4").Value = "=AND(TEXT(RIGHT(A11, 8), ""hh:mm:ss"") >= ""02:00:00"",TEXT(RIGHT(A11, 8), ""hh:mm:ss"") <= ""21:30:00"")"
   
4.    ' Range("A11").Select
   
5.    ' Range(Selection, Selection.End(xlToRight)).Select
   
6.    ' Range(Selection, Selection.End(xlDown)).Select
   
7.     Range("A10:EQ5482").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
   
8.         Range("E3:E4"), Unique:=False
   
9. End Sub

½Æ»s¥N½X

play9091

¦^´_ 12# GBKEE

¤W¶Ç¿ù½d¨Ò¤F¡K¡K¨º­Ó½d¨ÒÀɬO¥i¥H¦¨¥\¿z¿ïªº¡K¡K
¤W¶Ç¥t¥~¤@­ÓÀÉ¡A¦P¼Ëªº±¡ªp¡A°µ¦P¼Ëªº¿z¿ï¡A¦ý¬O½T¿z¿ï¤£¥X¨Ó¡K¡K§Úªº°ÝÃD¬O³o­Ó¡Kª©¥DÀ³¸Ó·|²M·¡§Úªº°ÝÃD

GBKEE

¦^´_ 11# play9091
¨S°ÝÃD, ³o¼Ë¶i¶¥¿z¿ï¹ïªü.
¤u§@ªí¨ç¼Æ ISLOGICAL °Ñ·Óªº­È¬OÅÞ¿è­È ¶Ç¦^ TRUE    ,E4 ¶Ç¦^ ¬°TRUE ©ÎFALSE ¦P¼Ë¬OÅÞ¿è­È,
=IF(ISLOGICAL(E4)," & "" & ",A10)"    ¥Ã»·¶Ç¦^ ""     ³o¤½¦¡¬O»~¥´»~¼²ªº

1. Sub ttt()
   
2.     Sheets("Sheet1").Range("E3") = ""   
   
3.    '©Î Sheets("Sheet1").Range("E3") = "ABC"
   
4.   ' ¶i¶¥¿z¿ï :    ¿z¿ï·Ç«h ¬O­pºâ¬O·Ç«h(±ø¥ó¸Ì¦³­pºâ¤½¦¡)  , ·Ç«hÄæ¦ì: ¤£¥i¬O¸ê®Æ®w¤ºªºÄæ¦ì (¥i¥H¬OªÅ¥ÕÄæ¦ì)
   
5.     '­pºâ¬O·Ç«h    ¦pE4=  A10   ¬O¿z¿ï¤£¨ì¸ê®Æ
   
6.     Sheets("Sheet1").Range("E4").Value = "=AND(TEXT(RIGHT(A11, 8), ""hh:mm:ss"") >= ""02:00:00"",TEXT(RIGHT(A11, 8), ""hh:mm:ss"") <= ""21:30:00"")"
   
7.     Range("A10").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
   
8.         Range("E3:E4"), Unique:=False
   
9. End Sub

½Æ»s¥N½X

play9091

¥IÀɬ°§Úªº½d¨ÒÀÉ¡K¡K
¦bE3©ME4¿é¤J¤½¦¡¡A¥Î¡u¶i¶¥¿z¿ï¡v¡K¡K¸Ô¨£VBE

GBKEE

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

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

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

¨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