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

[µo°Ý] ¦p¦ó§Q¥Îªí³æ§@¥X³fªí

  1. Sub zz()
  2. Dim a, Title, b(), c&, n&, k&, r&, t&
  3. Title = [b2:f2].Value
  4. c = UBound(Title, 2)
  5. a = [b2].CurrentRegion.Value
  6. t = UBound(a, 2) / c - 1
  7. ReDim b(1 To UBound(a) * (t + 1), 1 To c + 1)
  8. For n = 0 To t
  9.     k = n * c + c
  10.     For i = 2 To UBound(a)
  11.         If a(i, k) Then
  12.             r = r + 1
  13.             For j = 1 To c
  14.                 b(r, j) = a(i, k - c + j)
  15.             Next
  16.             b(r, c + 1) = b(r, 4) * b(r, 5)
  17.         End If
  18.     Next
  19. Next
  20. Workbooks.Add 1
  21. [a1].Resize(1, c) = Title
  22. Cells(1, c + 1) = "Amount"
  23. [a2].Resize(r, c + 1) = b
  24. [a1].CurrentRegion.Borders.Value = 1
  25. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# dou10801
  1. Sub zz()
  2. Dim a, Title, b(), c&, n&, k&, r&, t&
  3. Title = [b2:f2].Value   '¨ú¼ÐÃD
  4. c = UBound(Title, 2)    '¨ú¼ÐÃDªø«×
  5. a = [b2].CurrentRegion.Value '¨úªí1ªº¸ê®Æ©ñ¤J a Array
  6. t = UBound(a, 2) / c - 1    '­pºâªí1¤¤¼ÐÃDªº­«½Æ¦¸¼Æ ¥H 0 ¶}©l¦Ü t, ¬°¥H¤U²Ä¤@´`Àô§@¦n­pºâ ,½Ð¬Ý @_@
  7. ReDim b(1 To UBound(a) * (t + 1), 1 To c + 1)   '«Ø¥ß¤@­Ó¸û¤jªº b Array
  8. For n = 0 To t  '@_@
  9.     k = n * c + c   '­pºâ k ªº­È, §Y¥X³f¼Æ¶q¦bTitle¤¤ªº¦ì¸m
  10.     For i = 2 To UBound(a)  '¥Ñ a ªº²Ä2¦æ´`Àô
  11.         If a(i, k) Then '¦æ¦C¹ïÀ³¦³¥X³f¼Æ¶q, ¶i¦æ¥H¤Uµ{§Ç
  12.             r = r + 1   '«Ø¥ß·s¦æ¼Æµ¹ b Array
  13.             For j = 1 To c  '´`Àô¼ÐÃDªø«×
  14.                 b(r, j) = a(i, k - c + j)   '¨ú a Array ¤¤¬Û¹ï¦æ¦Cªº¸ê®Æµ¹ b Array
  15.             Next            '´`Àô
  16.             b(r, c + 1) = b(r, 4) * b(r, 5) '­pºâª÷ÃB
  17.         End If
  18.     Next    '´`Àô
  19. Next    '´`Àô
  20. Workbooks.Add 1 '·s«Ø¤u§@ï
  21. [a1].Resize(1, c) = Title   '¼g¤J¼ÐÃD
  22. Cells(1, c + 1) = "Amount" '¼g¤J·s¼ÐÃD
  23. [a2].Resize(r, c + 1) = b   '±N b Array ¼g¤J
  24. [a1].CurrentRegion.Borders.Value = 1    'µe¤W®æ½u
  25. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD