¦A½Ð±Ð¤@­ÓVBA»yªkªº°ÝÃD

skyutm

¦U¦ì¥ý¶i¤j®a¦n¡I
        ¤p§Ì¬O¦ì°ê¤p±Ð®v¡A·|·Q­n±µÄ²VBA¬O¦]¬°ªA°Èªº¾Ç®Õ¨Ã¨S¦³¦¨ÁZ­pºâ³nÅé¡A©Ò¥H¤p§Ìµo¼«¹Ï±j¡A¦Û´»°²¶}©l¬ãŪ¦³ÃöVBAªº®ÑÄy
       ¡A¥Ø«e¤w¬Ý§¹Excel VBA¶W¹Ï¸Ñ³o¥»®Ñ¡A¦ý¬O¦b¦¨ÁZ³nÅ骺³]­p¤W¤´µM§äµLÀYºü¡A©Ò¥H§Æ±æºô¸ô¤Wªº¦U¦ì¥ý¶i¥i¥H´£¨Ñ¸Ñ´bªºµª®×¡CÁÂÁ«ü±Ð¡I
        ­º¥ý¡A²Ä¤@­Ó°ÝÃD¬O¡G§Ú·Q­n¦bExcelªº²Ä¤@­Ó¤u§@ªí¤W³]©w¥i¥H¶ñ¤J¸ê®ÆªºªÅ®æ¡A¸ê®ÆÄ榳¤@­ÓªÅ¦ì¬O¶ñ¤H¼Æ¡A¦Ó©Ò¶ñ¤Jªº¤H¼Æ«h¥i¥H±±¨î²Ä¤G­Ó¤u§@ªíÀx¦s         
         ®æªº¼Æ¶q¡A¡]·N«ä´N¬O°ò¥»Äæ¶ñ¤J20¤H¡A¨º²Ä¤G­Ó¤u§@ªí«h·|¦C¥X20­ÓªÅ®æ¥H¶ñ¤J¾Ç¥Í©m¦W¡^¡A½Ð°Ý¸Ó§Q¥Î­þ¨Ç«ü¥O©Î¨ç¼Æ¡C·PÁ¸Ѵb¡I

kevin681024

¦^´_ 1# skyutm

¤£ª¾¦³µL¦^ÂШì°ÝÃD
½Ð°Ñ¦Òªþ¥ó
­Yªþ¥óµLªk¤U¸ü¡A½Ð¦Ü¤U¦Cºô§}¤U¸ü
http://www.funp.net/664276
    Sample.zip (12.62 KB)

¤U¸ü¦¸¼Æ: 16

2012-8-10 14:28

skyutm

«z¡I¯u¬O¼ö¤ß§U¤H¥B¨³³t½T¹ê¡A·PÁ¤j¤j¸Ñµª¡A¤p§Ì¤w¤U¸üÀÉ®×´ú¸ÕµL»~¡C½Ðµ¥§Ú¥´¶}½s¿è¾¹¦n¦n¬ã¨s¤@¤U«ü¥O¡C¦³°ÝÃD¦A¨Ó½Ð±Ð¡A¥ýÁ°աI

brabus

¸ò§A»¡³á
³o¨àªº¦P¾Ç¬O«Ü¼ö¤ßªº
§A¥i¥H¦h¦h´£°Ý
§Ú¶¶«K¥i¥H¾Ç²ß©O

skyutm

¦U¦ì¥ý¶i¤j®a¦n¡I
       «D±`·PÁ¤j®aªº¨ó§U¡C¤p§Ì©|¦³²Ä¤G­Ó°ÝÃD·Q­n½Ð±Ð¡C°ÝÃD¦p¤U¡G
       1.²Ä¤@­Ó¤u§@ªí¬O³]©wÄæ¡B²Ä¤G­Ó¤u§@ªí¬Oµn°O¦¨ÁZÄæ¦pªG¦b³]©wÄæ¶ñ¤J¦~¬q¡B¯Z¯Å¡B¤H¼Æ¡C¨Ò¦p 3¦~6¯Z15¤H¡B¦¨ÁZÄæ¦ì´N·|¥X²{30601~30615ªº15­ÓÄæ¦ì
       §Ú¸Õ¤F¤@¬q®É¶¡¡C¼Æ¦r¤W¥u¯à¥X²{1¡BµLªk¥X²{01¡A¥Ø«e§Ú¤w·Q¥X¦p¤Uªº»yªk¡A¦ý¬OµLªk°õ¦æ¡A½Ð¤j®a¸Ñ´b¤@¤U¡A¯u¬O·P¿E¡G
Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$b$3" Then
    Dim i As Integer
    For i = 1 To Target.Value
    Worksheets("sheet2").Cells(i + 2, "a") = i
    Next Target.Value
    End If
End Sub

white945

¦^´_ 1# skyutm

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Intersect(Target, [B1:B3]) Is Nothing Then Exit Sub
   
3. ReDim ar(1 To [B3])
   
4. YC = [B1] & Format([B2], "00")
   
5. For i = 1 To [B3]
   
6.   ar(i) = YC & Format(i, "00")
   
7. Next
   
8. Sheets(2).[A:A] = ""
   
9. Sheets(2).[A1].Resize([B3], 1) = Application.Transpose(ar)
   
10. End Sub

½Æ»s¥N½X

skyutm

·PÁ¤j¤j¸Ñ´b¡A¤p§Ì¥ý¥h¬ã¨s¤@¤U¡C¦A¬Ý±¡§Î¦p¦ó¡C¥ýÁÂÅo¡I

skyutm

·PÁÂwhite945¤j¤jªº¦^ÂСA¦ý¬O¦³¤@­Ó¤p°ÝÃD¡A¦]¬°[B1:B3]¬O¥ý¶ñB1©Ò¥H·|¥X²{°}¦C¶W¥X¯Á¤Þ½d³òªº¿ù»~°T®§¡A§Ú²q¥i¯à¬O¦]¬°B3ÁÙ¨S¶ñ¼Æ¦r   ReDim ar(1 To [B3])
¥t¥~ÁÙ¦³
©Ó»X¤j®aªºÀ°§U¡A§Ú§â«e­±¨â¦ì¦n¤ß¤Hªº»yªk¥[¥H­×§ï«á¡AÅܦ¨¤U¦C¨â¦¡¦ý¬O¦A§â¥¦­Ì©ñ¦b²Ä¤@­Ó¤u§@ªíªº®É­Ô«o·|¥X²{°ÝÃD¡A·Q½Ð°Ý½×¾Âªº¤j¤j¡A°ÝÃD¬O¥X¦b­þ¸Ì¡A·PÁ«ü±Ð¡I
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$J$3" Then
        If Sheet2.Range("A1048576").EntireRow.Hidden Then
            Sheet2.Cells.EntireRow.Hidden = False
            Sheet2.Cells.EntireColumn.Hidden = False
        End If
        Sheet2.Range(Sheet2.Range("A" & Target.Value + 3), Sheet2.Range("A3").End(xlDown)).EntireRow.Hidden = True
        Sheet2.Range(Sheet2.Range("W1"), Sheet2.Range("W1").End(xlToRight)).EntireColumn.Hidden = True
    End If
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [J1:J3]) Is Nothing Then Exit Sub
    ReDim ar(1 To [J3])
    YC = [J1] & Format([J2], "00")
    For i = 1 To [J3]
    ar(i) = YC & Format(i, "00")
    Next
    Sheets(2).[A:A] = ""
    Sheets(2).[A3].Resize([J3], 1) = Application.Transpose(ar)
End Sub

white945

¦^´_ 9# skyutm
¨â­Óµ{¦¡½XÀ³¸Ó¦X¨Ö

1. Sub Worksheet_Change(ByVal Target As Range)
   
2.     If Intersect(Target, [J1:J3]) Is Nothing Or [J3] = 0 Then Exit Sub
   
3.     ReDim ar(1 To [J3])
   
4.     YC = [J1] & Format([J2], "00")
   
5.     For i = 1 To [J3]
   
6.     ar(i) = YC & Format(i, "00")
   
7.     Next
   
8.     With Sheets(4)
   
9.     .Cells.Rows.Hidden = False
   
10.     .Cells.Columns.Hidden = False
    
11.     .[A:A] = ""
    
12.     .[A3].Resize([J3], 1) = Application.Transpose(ar)
    
13.     .Range("C1", .Cells(1, .Columns.Count)).EntireColumn.Hidden = True
    
14.     .Range(.[A3].Offset([J3], 0), .Cells(Rows.Count, 1)).EntireRow.Hidden = True
    
15.     End With
    
16. End Sub

½Æ»s¥N½X

skyutm

·PÁÂwhite945¤j¤jªº¦^ÂСA§Ú¥ý¥h°µ¤@¤U¥\½Ò¡A¦³°ÝÃD¦A¨Ó½Ð±Ð¡A¥ýÁÂÁ¤F¡I