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

[µo°Ý] ½Ð±Ð¡A¦p¦ó½Æ»s¤£¦P¤u§@ªí¯S©wÄæ¦ì(©¿²¤ªÅ¥Õ­È)¨ì¤@­Ó¤u§@ªí¤W

[µo°Ý] ½Ð±Ð¡A¦p¦ó½Æ»s¤£¦P¤u§@ªí¯S©wÄæ¦ì(©¿²¤ªÅ¥Õ­È)¨ì¤@­Ó¤u§@ªí¤W

¦U¦ì¤j¯«¦n¡A¤p§Ì¤µ¤Ñ¹J¨ì§Ú¦³¤£¦Pªº¤u§@ªí¡A¤j·§10­Ó(¤£¦P­Ó¦WºÙ¡A¦p XXXX-³æ»ù¤ÀªR)
¥L­Ìªº®æ¦¡³£¤@¼Ë¡A¤µ¤Ñ·Q§â¥L­Ì¥þ³¡·J¾ã¨ì¤@­Ó¤u§@ªí¡A­ì¥»¨Ï¥Î¿ý»s¥¨¶°¡A
¦ý¬O§Ú¤£ª¾¹D¦p¦ó¥[¼g Åý¥¦¦Û°Ê§PÂ_ ¨C­Óªí®æ¤§¶¡³£·|¹j¤@¦C¡C

Àɮצpªþ¥ó¡ã
1090722-TEST.zip (25.41 KB)

³æ»ù¤ÀªRÁ`ªí ´N¬O§Ú­nªºµ²ªG ¦A«ô°U¦U¦ì¤j¤j«üÂI°g¬z

¦^´_ 1# edmondsforum

¸Õ¸Õ¬Ý¬O§_²Å¦X
Sub ex()
Dim sht As Object
Dim r%
r = 6
Sheets("³æ»ù¤ÀªRÁ`ªí").Cells.Clear
For Each sht In Worksheets
   If sht.Name Like "*³æ»ù¤ÀªR" Then
      With Sheets(sht.Name)
         .Range(.[b2], .Cells(.[c65535].End(3).Row, 8)).Copy Sheets("³æ»ù¤ÀªRÁ`ªí").Cells(r, 2)     
         r = r + .[c65535].End(3).Row
      End With
   End If
Next
With Sheets("³æ»ù¤ÀªRÁ`ªí")
   .Cells.Font.Name = "µØ±dÁõ®ÑÅéW5"
   .[b5].Value = "¶µ¦¸"
   .[b5].HorizontalAlignment = xlCenter
   With .Range("B2:H2")
      .Merge
      .Value = "·PÁ³»¶®a±Ú°Q½×ª©"
      .HorizontalAlignment = xlCenter
      .Font.Bold = True
      .Font.Size = 16
   End With
   With .Range("B3:H3")
      .Merge
      .Value = "³æ»ù¤ÀªRªí"
      .HorizontalAlignment = xlCenter
      .Font.Underline = xlUnderlineStyleSingle
      .Font.Size = 14
   End With
   .Range("c4:H4").Merge
   .[c4].Value = "¤uµ{¦WºÙ¡G³Â»¶®a±Ú°Q½×ª©"
   .Range("c5:H5").Merge
   .[c5].Value = "¤uµ{½s¸¹¡GExcelvba"
   .Range(.[b6], .Cells(.[c65535].End(3).Row, 8)) = .Range(.[b6], .Cells(.[c65535].End(3).Row, 8)).Value  
   .Columns("A:I").AutoFit
End With
End Sub

TOP

Sub TEST()
Dim xR As Range, xS As Worksheet, xU As Range
With Sheets("³æ»ù¤ÀªRÁ`ªí")
     .UsedRange.Offset(5, 0).EntireRow.Delete
     Set xR = .[B6]
End With
For Each xS In Sheets
    If Right(xS.Name, 4) <> "³æ»ù¤ÀªR" Then GoTo 101
    Set xU = Intersect(xS.[B:H], xS.UsedRange).Offset(1, 0)
    With xR.Resize(xU.Rows.Count, xU.Columns.Count)
         xU.Copy .Cells
         .Value = .Value
    End With
    Set xR = xR(xU.Rows.Count + 1)
101: Next
Range(xR(-xR.Row + 3, 1), xR(-1, 7)).Name = "Print_Area"
End Sub

²Ä¢µ¦æ¥H¤W¦Û¦æ³B²z¡ã¡ã
¡@
¡@
¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×

TOP

¦^´_ 2# jcchiang

ÁÂÁÂJ¤j ¬Û·í¦¨¥\!!
³oÃ䦳¨â­Ó°ÝÃD·Q½Ð±Ð¡G
1.³æ»ù¤ÀªRÁ`ªíªº¶µ¦¸¡A¯à¨Ì·Ó¦U¦a¤l¶µ³æ»ù¤ÀªR½s¸¹ ¤@ ¤G ¤T ¥| ¤­ ¨Ì·Ó±Æ§Ç½Æ»s¹L¨Ó¶Ü?
   §Ú¦³¸ÕµÛ§ï ±Æ¤ô·¾-³æ»ù¤ÀªR §â¥¦§ï¦¨ ¤­ ¡A¥¦ÁÙ¬O±Æ²Ä¤@­Ó¡C

2.¯àÅý³æ»ù¤ÀªRÁ`ªí¥þ³¡³£Åܦ¨¶Â¦â¶Ü?  (ÁöµM¤â°Ê§ï´N¦nXD) §Úµo²{ ³æ¦ì ¨ºÃä³£·|Åܦ¨ ¬õ¦â

TOP

¦^´_ 3# ­ã´£³¡ªL


    ·PÁ­ã¤j!!!  §Ö¬½·Ç­C!!
    ·Ç¤j¡A§Ú³oÃ䪺°ÝÃD¤]¬O¤@¼Ë¡A

   1.³æ»ù¤ÀªRÁ`ªíªº¶µ¦¸¡A¯à¨Ì·Ó¦U¤l¶µ³æ»ù¤ÀªR½s¸¹ ¤@ ¤G ¤T ¥| ¤­ ¨Ì·Ó±Æ§Ç½Æ»s¹L¨Ó¶Ü?
      §Ú¦³¸ÕµÛ§ï ±Æ¤ô·¾-³æ»ù¤ÀªR §â¥¦§ï¦¨ ¤­ ¡A¥¦ÁÙ¬O±Æ²Ä¤@­Ó¡C

   2.¯àÅý³æ»ù¤ÀªRÁ`ªí¥þ³¡³£Åܦ¨¶Â¦â¶Ü?  (ÁöµM¤â°Ê§ï´N¦nXD) §Úµo²{ ³æ¦ì ¨ºÃä³£·|Åܦ¨ ¬õ¦â

TOP

¤£¦n·N«ä¡A§Ñ¤F´£¤Î¤@¥ó¨Æ
´N¬O ³æ»ù¤ÀªRÁ`ªíªº Äæ¼e ¯à«O«ù¤£ÅܶÜ
¦]¬°§Ú­ì¥»´N³]©w¦n¤F

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-7-28 12:52 ½s¿è

¦^´_ 5# edmondsforum


1.³æ»ù¤ÀªRÁ`ªíªº¶µ¦¸¡A¯à¨Ì·Ó¦U¤l¶µ³æ»ù¤ÀªR½s¸¹ ¤@ ¤G ¤T ¥| ¤­ ¨Ì·Ó±Æ§Ç½Æ»s¹L¨Ó¶Ü?  

1) ³Ì¦h¨ì´X? 10¥H¤W,¤S¬O«ç»ò¼Ð?
   10 = "¤@¤Q" ©Î "¤Q" ©Î  "¤@¡³"
   12 = "¤Q¤G" ©Î "¤@¤Q¤G"

2) ¦P¤@¤u§@ªí¡A·|¤£·|¸õ¸¹¡H ©Î¦P¸¹
3) ¤£¦P¤u§@ªí, ·|¤£·|¦P¸¹?

¤¤¤å¤j¼gªº¼Æ¦r, ³o¤£¤Ó¦n§Ë~~³Ì¦n¦Û¦æ¥ý±N¤u§@ªí¶¶§Ç¤â°Ê½Õ¤@½Õ~~©Î©w¸q¤u§@ªí¦WºÙ¶¶§Ç
¦Ü©ó¤å¦rÃC¦â¤Î®æ¦¡, À³¸Ó¤£Ãø, ¥i¦Û¦æ¥h­×§ï©Î¸É¤Jµ{¦¡½X~~¼Ë¼Ë¦ù¤âµP¤£¬O¦n²ßºD


======================================

TOP

¥»©«³Ì«á¥Ñ jcchiang ©ó 2020-7-29 10:51 ½s¿è

¦^´_ 4# edmondsforum


­ã¤j¤w¸gÂI¥X«Ü¦h¥i¯àªº°ÝÃD,¥ý¥HÀɮתº¸ê®Æ°µµ{¦¡½Õ¾ã,¨ä¾l³¡¥÷½Ð¦Û¦æ­×§ï

Sub ex1()
Dim arr, a, c, B%, QQ%, R%
Dim sht As Object
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
Sheets("³æ»ù¤ÀªRÁ`ªí").Cells.Clear
arr = Array("¤@", "¤G", "¤T", "¥|", "¤­")  '¤uµ{¶µ¦¸
For Each sht In Worksheets
   If sht.Name Like "*³æ»ù¤ÀªR" Then
      With Sheets(sht.Name)
         For Each a In .Range(.[b2], .[b65535].End(3))
            For x = 0 To UBound(arr)
               If a.Value = arr(x) And Not d.Exists(a.Value) Then d.Add a.Value, sht.Name & "@" & a.Address   
            Next
         Next
      End With
   End If
Next
R = 6
For Each a In arr
   For B = 0 To d.Count - 1
      If a = d.keys()(B) Then
         c = Split(d.items()(B), "@")
         With Sheets(c(0))
            For QQ = 1 To 100
               If .Range(c(1)).Offset(QQ, 1) = "¤p ­p" Then Exit For
            Next
            .Range(c(1)).Resize(QQ + 2, 8).Copy Sheets("³æ»ù¤ÀªRÁ`ªí").Cells(R, 2)
            R = R + QQ + 3
         End With
       End If
      Next
   Next
With Sheets("³æ»ù¤ÀªRÁ`ªí")
   .Cells.Font.Name = "µØ±dÁõ®ÑÅéW5"
   .Cells.Font.ColorIndex = 1
   .[b5].Value = "¶µ¦¸"
   .[b5].HorizontalAlignment = xlCenter
   With .Range("B2:H2")
      .Merge
      .Value = "·PÁ³»¶®a±Ú°Q½×ª©"
      .HorizontalAlignment = xlCenter
      .Font.Bold = True
      .Font.Size = 16
   End With
   With .Range("B3:H3")
      .Merge
      .Value = "³æ»ù¤ÀªRªí"
      .HorizontalAlignment = xlCenter
      .Font.Underline = xlUnderlineStyleSingle
      .Font.Size = 14
   End With
   .Range("c4:H4").Merge
   .[c4].Value = "¤uµ{¦WºÙ¡G³Â»¶®a±Ú°Q½×ª©"
   .Range("c5:H5").Merge
   .[c5].Value = "¤uµ{½s¸¹¡GExcelvba"
   .Range(.[b6], .Cells(.[c65535].End(3).Row, 8)) = .Range(.[b6], .Cells(.[c65535].End(3).Row, 8)).Value
End With
Set d = Nothing
End Sub

TOP

¦^´_ 7# ­ã´£³¡ªL

¦^ÂЭã¤j
1. ¤Q¥H¤W´N¬Oª½±µÅܦ¨ ¤Q¤@ ¤Q¤G¡B¤G¤Q¤@ ¡B¤T¤Q¤­¤§Ãþªº¡C
2.¦P¤@­Ó¤u§@ªí¤£¤Ó¥i¯à¸õ¸¹¡C
3.¤£¦P¤u§@ªí¤£·|¦P¸¹¡C
¨º¦pªG§ï¦¨¤¤¤å¼Æ¦r 1 2 3 4 5 6... 10 11 21 31 ¬O§_¥i¦æ©O

¤£¦n·N«ä ­ã¤j¡I§Ú·|ºÉ¶q¾a¦Û¤v¥h§äµ{¦¡½X!! :P

TOP

¦^´_ 8# jcchiang


  ÁÂÁ¤j¤j¡A¤¤¤å¤j¼g±Æ§ÇªGµM«Ü³Â·Ð¡A
  ³Ì«á¡A¦pªG¬O¥H¯Â¼Æ¦r±Æ¦C©O¡A¨Ò¦p¤uµ{¶µ¦¸¸Ì­±¬O 1 2 3 4 5 ¡A 10 21 35 41 ¤§Ãþªº¸Ü
  ³o¼Ë¥i¥H¤£¥Î¤â§ï¤F¶Ü? ¦A«ô°U§A¤F

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD