stillfish00

¦^´_ 1# konkon3141
¥ý¨ìEXCEL¿ï¶µ¤Ä¿ï¶}µo¤H­û , ¶i¤JVisual Basic
¥ª¤è¿ï²Ä¤@­Ó¤u§@ªí , ½Æ»s¥H¤U¥N½X

1. Private Sub Worksheet_Activate()
   
2.     'Q1:¦C¥X¤u§@ªí²M³æ
   
3.     Dim i
   
4.     Range(Range("A1"), Range("A1").End(xlDown)).ClearContents   '¥ý²M°£
   
5.     For i = 3 To Sheets.Count   '¤£§t«e¨â­Ó¤u§@ªí(¥Ø¿ý,«¬¿ý)
   
6.         Range("A1").Offset(i - 3, 0) = Sheets(i).Name
   
7.     Next
   
8.    
   
9.     'Q2:­pºâÁ`¼Æ
   
10.     Range("B1") = "=COUNTA(" & Sheets(3).Name & ":" & Sheets(Sheets.Count).Name & "!B:B)" & "-COUNTA(" & Sheets(3).Name & ":" & Sheets(Sheets.Count).Name & "!B1:B8)"
    
11. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 4# konkon3141
alt +F1¶i¤JVisual Basic , ¥ªÃä§ä¨ì¥Ø¿ý³o­Ó¤u§@ªíÂI¨â¤U

¤U¸ü (82.43 KB)

2012-10-28 11:21

¦b¥kÃä°Ï°ì¶K¤W³o¬qcode:

1. Private Sub Worksheet_Activate()
   
2.     'Q1:
   
3.     Range("C9") = Sheets.Count - 2
   
4.    
   
5.     'Q2:-
   
6.     Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
   
7.     Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
   
8.     Range("C14") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
   
9. 
   
10. End Sub

½Æ»s¥N½X

¦h¸Õ¸Õ ¬Ý¯à¤£¯à²Å¦X§Aªº»Ý¨DÂP
Book1.zip (1.68 KB)

¤U¸ü¦¸¼Æ: 7

2012-10-28 11:17

stillfish00

¦^´_ 6# konkon3141
®¦..§Ú¤£¾å±o¬O¤£¬Oª©¥»¤£¦P³y¦¨ªº, §Ú¥u¦³2010°õ¦æ³£¥¿±` , §A¸Õ¸Õ
1.¨Ï¥Î«e­±ªºªþ¥ó®É¯à¥¿±`°õ¦æ¶Ü?
2.Àˬd¥Ø¿ý¤u§@ªí¬O§_¦³«OÅ@?
3.µ{¦¡Range«e«ü©wSheets("¥Ø¿ý") , ¬Ý¦³¨S¦³®t²§?

1. Private Sub Worksheet_Activate()
   
2.     'Q1:
   
3.     Sheets("¥Ø¿ý").Range("C9") = Sheets.Count - 2
   
4.    
   
5.     'Q2:
   
6.     Sheets("¥Ø¿ý").Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
   
7.     Sheets("¥Ø¿ý").Range("C11") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
   
8.     Sheets("¥Ø¿ý").Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
   
9. 
   
10. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 9# konkon3141
¤º®e´N¬O¦bC10 ¶ñ¤J¤½¦¡
=COUNTA(¶}©l¤u§@ªí:µ²§ô¤u§@ªí!B:B)-COUNTA(¶}©l¤u§@ªí:µ²§ô¤u§@ªí!B1:B8)
§Y­pºâ  ©Ò¦³¶}©l¤u§@ªí~µ²§ô¤u§@ªí BÄæ «DªÅ¥Õ³æ¤¸®æÁ`¼Æ
    ´î¥h  ©Ò¦³¶}©l¤u§@ªí~µ²§ô¤u§@ªí B1¨ìB8 «DªÅ¥Õ³æ¤¸®æÁ`¼Æ

¦]¬°¤u§@ªí¥i¯à¼W¥[ , ¤£¬O©T©w¦P¤@­Ó , ©Ò¥H¥ÎVBA ,
Sheets(Sheets.Count - 1).Name ¥h§ä¨ì³Ì«á¤@­Ó¤u§@ªí¦W¦r

C11, C12¨Ì¦¹Ãþ±À , §Ú¤]¤£©ú¥Õ­þ¸Ì¦³°ÝÃD

stillfish00

¦^´_ 11# konkon3141
http://d.pr/f/Sxsz

³Ì«áµo²{°ÝÃD¦b¤u§@ªí¦WºÙ¦³§t¬A¸¹ , ©Ò¥H¦b¨Ï¥Î¤½¦¡®É¤u§@ªí¦WºÙ­n¥t¥~¥[³æ¤Þ¸¹¤~¤£·|¥X¿ù
°Ú°Ú~³oºâ§Ú¨S¾i¦¨¨}¦n²ßºD°Õ><

stillfish00

¦^´_ 13# konkon3141
http://d.pr/f/Sxsz ³o­ÓÀɮפ£¯à¥Î¶Ü? ³o¬O§ï¹Lªº

stillfish00

¦^´_ 16# konkon3141
ÁôÂäu§@ªíªº¶W³sµ²·|¥¢®Ä¬O¥¿±`ªº..
¥t¤@ºØ¤èªk¬O¤u§@ªí¤£ÁôÂà , ¨ìExcel¿ï¶µ¸Ì¨ú®ø ¤u§@ªí¯Á¤Þ¼ÐÅÒ
¤£¹L¬Ý¥Î³~°Õ  ¦³®É­Ô·|¤£¤Ó¤è«K..