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

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

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

¥»©«³Ì«á¥Ñ papaya ©ó 2019-3-3 15:38 ½s¿è

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

ªþ¥ó¡J
BASE.rar (202.79 KB)
·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¦ì!

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

TOP

¦^´_ 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
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 3# stillfish00

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

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD