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

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

½Ð±Ð¦³ÃöVBAªº»yªk¡I

¦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

TOP

¦^´_ 1# skyutm

¤£ª¾¦³µL¦^ÂШì°ÝÃD
½Ð°Ñ¦Òªþ¥ó
­Yªþ¥óµLªk¤U¸ü¡A½Ð¦Ü¤U¦Cºô§}¤U¸ü
http://www.funp.net/664276
    Sample.zip (12.62 KB)
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

«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

TOP

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

TOP

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

¦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

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

TOP

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

TOP

·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

TOP

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

TOP

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

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD