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

[µo°Ý] ¦p¦óÅý¤£¦PÀɮצP¤@³f¸¹¦Û°Ê¶×¤JÁ`ªí

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-9-23 16:49 ½s¿è

¦^´_ 1# s7659109
¸Õ¸Õ¬Ý
  1. Sub Ex_¯S©w­È·J¾ã¨ìÁ`ªí()
  2.     Dim xNO As String, AR(), A, Sh As Worksheet, R As Range, i As Integer, xR As Integer
  3.     xNO = InputBox("¿é¤J³f¸¹")
  4.     If xNO = "" Then Exit Sub
  5.     i = 1
  6.     For Each Sh In Sheets
  7.         If InStr(Sh.Name, "A0") = 1 Then
  8.             For Each R In Sh.Range("a1").CurrentRegion.Columns("B").Cells
  9.                 If R.Text = xNO Then
  10.                 A = Sh.Range("a1").CurrentRegion.Rows(R.Row)
  11.                 ReDim Preserve AR(1 To i)
  12.                 AR(i) = A
  13.                 i = i + 1
  14.                 End If
  15.             Next
  16.         End If
  17.     Next
  18.     If i = 1 Then MsgBox "³f¸¹: §ä¤£¨ì  " & xNO: Exit Sub
  19.     With Sheets("Á`ªí").Range("A1").CurrentRegion
  20.         .Parent.Activate
  21.         .Offset(2).Clear
  22.         .Range("A3").Resize(i - 1, UBound(A, 2)) = Application.Transpose(Application.Transpose(AR))
  23.          xR = Sheets("Á`ªí").Range("A1").CurrentRegion.Rows.Count
  24.         .Cells(3, 2).Resize(xR - 2).NumberFormatLocal = "@"
  25.         .Cells(3, 2).Resize(xR - 2).FormulaR1C1 = xNO
  26.         .Cells(xR + 1, 1) = "¦X­p"
  27.         .Cells(xR + 1, 5) = "=SUM(R[-1]C:R[-" & xR - 3 & "]C)"
  28.         .Cells(xR + 1, 5) = .Cells(xR + 1, 5)
  29.         .Cells(xR + 1, 8) = "=SUM(R[-1]C:R[-" & xR - 3 & "]C)"
  30.         .Cells(xR + 1, 8) = .Cells(xR + 1, 8)
  31.         .Cells(3, 2).Resize(xR - 2).NumberFormatLocal = "@"
  32.         .Cells(3, 2).Resize(xR - 2).FormulaR1C1 = xNO
  33.         'Rows (xR + 1)
  34.         .Rows(xR + 1).Interior.Color = vbYellow
  35.     End With
  36.     MsgBox "ok"
  37. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# s7659109
¥[¤WSh.
   
               If R.Text = xNO Then
10.                A = Sh.Range("a1").CurrentRegion.Rows(R.Row)
11.                ReDim Preserve AR(1 To i)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD