[µo°Ý] ¥H±±¨î¶µNrange¶ñ¤Jªº°_¨´´Á¼Æ²£¥Í®ÄªGÀɮסC

papaya

PS¡J°ÝÃD¦³Ãþ¦ü(­«½Æ)~½Ð²¾°£«eÃD¡C
ÁÂÁÂ!

ªþ¥ó¡J
BASE.rar (202.79 KB)

¤U¸ü¦¸¼Æ: 6

2019-3-3 15:31

·Q±NBASE(´£°Ý¥DÀÉ)ªºµ{¦¡½X¡A§ï¬°¦p¤U¦Cªº2­Ó»Ý¨D~
»Ý¨D1¡J³]¥ßNrange±±¨î¶µ¡A¥H§Q²£¥Í®ÄªGÀÉ®×~
·í±±¨î¶µ¤º¶ñ¤J1000¡A«h²£¥Í¦WºÙ="±Æ§Ç1000"ªº1­Ó®ÄªGÀɮסF
®ÄªGÀɮפº¦³¦WºÙ="Á`ªí"©M¦WºÙ="±Æ§Ç1000"µ¥2­Ó¤u§@ªí

·í±±¨î¶µ¤º¶ñ¤J1010-1020¡A«h²£¥Í¦WºÙ="±Æ§Ç1010-1020"ªº1­Ó®ÄªGÀɮסF
®ÄªGÀɮפº¦³¦WºÙ="Á`ªí"©M¦WºÙ="±Æ§Ç1010"¡A"±Æ§Ç1011"¡A.....¡A"±Æ§Ç1019"¡A"±Æ§Ç1020"µ¥12­Ó¤u§@ªí¡C

·í±±¨î¶µ¤º¶ñ¤J1000,1010-1020¡A«h²£¥Í¦WºÙ="±Æ§Ç1000"©M¦WºÙ="±Æ§Ç1010-1020"ªº2­Ó®ÄªGÀɮסF
2­Ó®ÄªGÀɮפºªº¤u§@ªí¦P¤W¡C

¨ä¾l¥H¦¹Ãþ±À¡C

·í¦³¦WºÙ¬Û¦Pªº®ÄªGÀɮײ£¥Í¡A«h·s²£¥Íªº®ÄªGÀÉ®×Âл\«e¤w²£¥Íªº®ÄªGÀɮסC

»Ý¨D2¡J¦U®ÄªG¤u§@ªíªº¤º®e~
¤u§@ªí¦WºÙ="Á`ªí"ªº¤º®e=½Æ»sBASE(´£°Ý¥DÀÉ)ªºDATA!A¡JJ¶K¤W"Á`ªí"!A1¡C
­áµ²µøµ¡"B2"

®ÄªGÀɮפº°£¤F¦WºÙ="Á`ªí"¥H¥~ªº¨ä¥L¦U®ÄªG¤u§@ªíªº¤º®e~
A1¶ñ¤J= Nrange±±¨î¶µ¤º¦U²Õ¦X¼Æ¦r(¥H³rÂI¤À¹j)¨Ã¼Ð¥Üª÷¦â¹Ï¼Ë
­áµ²µøµ¡"B2"

½Ð¸Ô¨£ªþ¥ó¡J±Æ§Ç1000(®ÄªGÀÉ)¡F±Æ§Ç1010-1020(®ÄªGÀÉ)

½Ð°Ý¡J
BASE(´£°Ý¥DÀÉ)ªºVBAµ{¦¡½X­n¦p¦ó­«·s½s¼g¡H
½Ð¦U¦ì¤j¤jÀ°¦£!ÁÂÁ¦U¦ì!

papaya

¦^´_ 3# stillfish00

·PÁ±zªºÀ°¦£©M«ü¾É

stillfish00

¦^´_ 2# papaya

1. Private Sub CommandButton1_Click()
   
2.     Dim text As String, istart As Integer, iend As Integer
   
3.     Dim file As String, ar
   
4.     text = InputBox("½Ð¿é¤J´Á¼Æ, (¦p: 1000,1010-1020)", "²£¥Í±Æ§ÇÀÉ")
   
5.     If Len(text) = 0 Then MsgBox "Bad Input": Exit Sub
   
6.     Application.DisplayAlerts = False
   
7.     Application.ScreenUpdating = False
   
8.     For Each s In Split(text, ",")
   
9.         file = ThisWorkbook.Path & "\±Æ§Ç" & Trim(s) & ".xls"
   
10.         ar = Split(Trim(s), "-")
    
11.         istart = CInt(ar(0))
    
12.         If UBound(ar) = 0 Then
    
13.             iend = istart
    
14.         Else
    
15.             iend = CInt(ar(1))
    
16.         End If
    
17.         
    
18.         If Len(Dir(file)) > 0 Then Kill file
    
19.         Set wb = Workbooks.Add()
    
20.         With wb
    
21.             For i = .Sheets.Count To 2 Step -1: .Sheets(i).Delete: Next
    
22.             With .Sheets(1)
    
23.                 .Name = "Á`ªí"
    
24.                 ThisWorkbook.Sheets("DATA").[A:J].Copy .[A1]
    
25.                 .Activate
    
26.                 .[B2].Select
    
27.                 ActiveWindow.FreezePanes = True
    
28.             End With
    
29.             .Sheets.Add After:=.Sheets(1), Count:=iend - istart + 1
    
30.             For i = 0 To iend - istart
    
31.                 With .Sheets(i + 2)
    
32.                     .Name = "±Æ§Ç" & istart + i
    
33.                     .[A1].Value = istart + i
    
34.                     .[A1].Font.Bold = True
    
35.                     .[A1].Interior.Color = 52479
    
36.                     .Activate
    
37.                     .[B2].Select
    
38.                     ActiveWindow.FreezePanes = True
    
39.                 End With
    
40.             Next
    
41.             .Sheets(1).Activate
    
42.             .SaveAs Filename:=file, FileFormat:=xlWorkbookNormal
    
43.             .Close False
    
44.         End With
    
45.     Next
    
46.     Application.ScreenUpdating = True
    
47.     Application.DisplayAlerts = True
    
48.     MsgBox "Finish"
    
49. End Sub

½Æ»s¥N½X

papaya

©êºp!ªþ¥óªº"±Æ§Ç1100-1020"(®ÄªGÀÉ)¡A¦WºÙ¦³µ§»~¡A¥¿½T¦WºÙÀ³¬°"±Æ§Ç1010-1020"(®ÄªGÀÉ)¡C
ÁÂÁÂ!