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

§â¿z¿ï¸ê®Æ¶K¦^¤u§@ªí1

§â¿z¿ï¸ê®Æ¶K¦^¤u§@ªí1

¤j¤j­Ì,·s¦~§Ö¼Ö,
½Ð±Ð¤@­Ó­×§ïµ{¦¡°ÝÃD, ¤U¦Cµ{¦¡¬O­Ó¿z¿ïÀÉ,¬O§â¤u§@ªí1¸ê®Æ¿z¿ï¨ì¤u§@ªí2,²{®É§Ú·Q§â¿z¿ï¸ê®Æ¶K¦^¤u§@ªí1aaÄæ¦ì¶}©l, ¸Ó¦p¦ó­×§ï, ½Ð¤j¤j¨ó§U, ÁÂÁÂ!!
Private Rng
Private Sub ComboBox1_Change()
[IU1] = Rng(2)
[IU2] = "*" & ComboBox1 & "*"
If ComboBox1 = "" Then [IU2] = "<>"
End Sub
Private Sub ComboBox2_Change()
If ComboBox2 = "" Then
[IV1] = Rng(3)
[IV2] = "<>"
Else
[IV1] = Rng(3) & "A"
[IV2] = "=YEAR(" & Rng(3) & ")=" & ComboBox2
End If
End Sub

Private Sub CommandButton1_Click()
Range("A4").CurrentRegion = ""
Rng.AdvancedFilter xlFilterCopy, [IU1:IU2], [A4]
End Sub
Private Sub CommandButton2_Click()
Range("A4").CurrentRegion = ""
Rng.AdvancedFilter xlFilterCopy, [IV1:IV2], [A4]
End Sub

Private Sub Worksheet_Activate()
Dim AA As String, MA, MI
Set Rng = Sheet2.Range("A2").CurrentRegion
ComboBox1.List = Rng.Columns(2).Offset(1).Value
AA = Rng.Columns(3).Offset(1).Address
MA = Format(Application.Max(Sheet2.Range(AA)), "YYYY")
MI = Format(Application.Min(Sheet2.Range(AA)), "YYYY")
ComboBox2.Clear
For I = MI To MA
ComboBox2.AddItem I
Next
End Sub

¤j¤j­Ì,·s¦~§Ö¼Ö,
½Ð±Ð¤@­Ó­×§ïµ{¦¡°ÝÃD, ¤U¦Cµ{¦¡¬O­Ó¿z¿ïÀÉ,¬O§â¤u§@ªí1¸ê®Æ¿z¿ï¨ì¤u§@ªí2,²{®É§Ú·Q§â¿z ...
317 µoªí©ó 2012-1-25 19:01



¸Õ¸Õ§âAdvancedFilter§ï¦¨³o­Ó¼Ë¤l?
Rng.AdvancedFilter xlFilterCopy, [IU1:IU2], worksheets("¤u§@ªí1").range("A4")
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

b]¦^´_ 2# kimbal
ÁÂÁ¤j¤j±Ð¾É, ¦ý§Ú¥\¤O«Ü²L, ¤£¦¨¥\, ²{§â¸ÓÀɤW¶Ç, ¤º¦³©ú²Ó, ½Ð¤j¤j­Ì¦A«×¨ó§U, ÁÂÁÂ!!

vv.rar (23.16 KB)

TOP

¦^´_ 3# 317


    µ{¦¡½X©ñ¦bSheets(a)¼Ò²Õ¤º
vv.rar (24.41 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh

½Ð±Ð¤j¤j, µ{¦¡½X©ñ¦bSheets(a)¼Ò²Õ¤º, ¤@¯ë¼Ò²Õ³£¬Omodule , ¦ÓSheets(a)¼Ò²Õ©ñ©ó­þ¦ì¸m, ½Ð±Ð¾É, ÁÂÁÂ!

TOP

¦^´_ 5# 317
Sheets(a)©óVBA¤¤¬Oª«¥ó(¤u§@ªíª«¥ó) : ª«¥ó¼Ò²Õ
ThisWorkbook ¤]¬O ,ª«¥ó¼Ò²Õ (¬¡­¶Ã¯ª«¥ó)
VBA¤¤Sheets(a)¥¦ªºCodeName ¬OSheet2 : CodeName ÄÝ©Ê: «ü©wª«¥óªº¥N½X¦WºÙ¡C°ßŪ String¡C

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-1-26 11:37 ½s¿è

¦^´_ 5# 317
³oÃþªº°ÝÃD­nÂç²M´X­ÓÆ[©À
1.§Aªºµ{¦¡½X³£¬O§Q¥Î¨Æ¥óµ{§Ç¡A©Ò¥H¡A¥²¶·²M·¡ªºª¾¹Dª«¥ó©ÒÄݪº¤u§@ªí
¨Ò¦p:ComboBox¤ÎCommandButtonµ¥ª«¥ó³£¬O¦bSheet2(a)¤u§@ªí¤ºªºª«¥ó¡A©Ò¥H¡A¥L­Ìªº¨Æ¥ó¥²¶·¦b¸Ó¤u§@ªí¼Ò²Õ¤º¤~¥iIJµo¡C
2.Worksheet_Activate¨Æ¥ó¬O¤u§@ªí§@¥Î®É©ÒIJµo¡A¦Ó§Aµ{¦¡½X¤º®e´N¬O¦b¤u§@ªía§@¥Î®É±NComboBoxªº¤º®e­«·s¥¬¸m¡A©Ò¥H¥²¶·¬O¦b¤u§@ªía¼Ò²Õ¤º¨Ï¥Î¡C
3.¦b¬¡­¶Ã¯¼Ò²Õ(Thisworkbook)©Î¤@¯ë¼Ò²Õ(Module)¤º«h¥²¶·«ü©w¤u§@ªí¡C
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Hsieh¤j¤j,GBKEE¤j¤j, 2¦ì¤j¤j¦w¦n, ¥ý¯¬¶P·s¦~§Ö¼Ö,
¬Ý¹L两¦ì¤j¤jµªÂÐ, ¬Û«H»Ý­n®É¶¡¬ã¨s, °ò©óÀɮ׫æ©ó§¹¦¨, Àµ½Ð¤j¤j­Ì¨ó§U, ¨ä¹ê§Ú·Q­nªº¬O·Q§â¸ê®Æ¿z¿ï©ó¦P¤@ªí¤ºauÄæ¦ì¤W,
Hsieh¤j¤j,¦^ÂЪºªþÀÉ, §Ú¸Õ¹L«á, ¸ê®ÆÁÙ¬O¿z¿ï©ó¤u§@ªí2¤W, ¬G¦A«×½Ð¨D¨ó§U, ±q两¦ì¤j¤jµªÂФ]ª¾¹D­ì¦]©Ò¦b, ¦ý¦]¥\¤O²L,
»Ý­n®É¶¡¤F¸Ñ, ³Ì«á§Æ±æ¯à¨ó§U, ÁÂÁÂ
88.rar (23.28 KB)

TOP

¦^´_ 8# 317
¤u§@ªíb¬O¦h¾lªº

    88.rar (26.83 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh
ÁÂÁª©¥D¤j¤j, °J¤ß·PÁÂ, ¯¬§Ö¼Ö

TOP

        ÀR«ä¦Û¦b : ¦n¨Æ­n´£±o°_¡A¬O«D­n©ñ±o¤U¡A¦¨´N§O¤H§Y¬O¦¨´N¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD