- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 44
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2025-2-20
               
|
¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-2-1 00:45 ½s¿è
¦^´_ 1# smartpearl
¸ê®Æ¥u¦³2ºØItem¶Ü?- Sub ex()
- Dim Ay(2), MyID%, Ary(2)
- Set d = CreateObject("Scripting.Dictionary")
- ar = Range("A1").CurrentRegion '±Nªí®æ¼g¤J°}¦C
- i = 2 '²Ä2¦C¶}©l
- Do
- j = 4 '²Ä4Äæ¶}©l
- Do
- MyID = IIf(ar(i, 2) = "Add", 0, 1) '²Ä2Äæ¦pªG¬OAdd´N¥Î0§_«h¥Î1§@¬°°}¦C¯Á¤Þ
- Ay(MyID) = Ay(MyID) + ar(i, j) '±N°}¦CȬۥ[
- j = j + 1 'Äæ¦ì¥[1
- Loop Until j > UBound(ar, 2) Or TypeName(Cells(1, j).Value) = "String" 'ª½¨ìÄæ¦ì¤j¹Lªí®æÄæ¦ì©Î¬O²Ä¤@¦CªºÈ¬O¦r¦ê(ÁקK²Ä2¦¸°õ¦æ·|pºâ¨ì쥻·s¼Wªº²Îp¦ì¸m)
- Cells(i, j).Resize(, 2) = Ay '¸ê®Æ¦Cªº«á±Äæ¦ì¥[¤J¥[Á`È
- Erase Ay
- i = i + 1 '¦C¼Æ¥[1
- Loop Until i > UBound(ar, 1) Or Cells(i, 1) = "" 'ª½¨ì¦C¼Æ¤j¹Lªí®æ¦C¼Æ©Î²Ä1Ä欰ªÅ¥Õ
- Cells(i, j) = "=SUM(R2C:R[-1]C)": Cells(i + 1, j + 1) = "=SUM(R2C:R[-1]C)" 'ªí®æ¥k¤U¤è¼g¤J¤½¦¡
- Cells(1, j).Resize(, 2) = Array("Count_Add", "Count_None") '¼g¤J¼ÐÃD
- j = 4
- Do
- i = 2
- Do
- d(ar(i, 2)) = ar(i, j) + d(ar(i, 2)) '¥H²Ä2Ä椺®e¬°¦r¨å¯Á¤Þ¡A¤À§OpºâÓ¯Á¤Þ¥[Á`
- i = i + 1
- Loop Until i > UBound(ar, 1) Or Cells(i, 1) = "" 'ª½¨ì¦C¼Æ¤j¹Lªí®æ¦C¼Æ©Î²Ä1Ä欰ªÅ¥Õ
- For Each ky In d.keys '¨ú¥X¨CÓ¦r¨å¯Á¤Þ
- Ay(s) = d(ky)
- s = s + 1
- Next
- Cells(i, j).Resize(3, 1) = Application.Transpose(Ay) 'ªí®æ¤U¤è¼g¤J¥[Á`¸ê®Æ
- Erase Ay: s = 0: d.RemoveAll '²M°£°}¦C»P¦r¨å¤º®e
- j = j + 1 'Äæ¼Æ¥[1
- Loop Until j > UBound(ar, 2) Or TypeName(Cells(1, j).Value) = "String" 'ª½¨ìÄæ¦ì¤j¹Lªí®æÄæ¦ì©Î¬O²Ä¤@¦CªºÈ¬O¦r¦ê(ÁקK²Ä2¦¸°õ¦æ·|pºâ¨ì쥻·s¼Wªº²Îp¦ì¸m)
- Cells(i + 2, 4).Resize(, Range("A1").CurrentRegion.Columns.Count - 3) = "=R[-1]C-R[-2]C" '¼g¤J¥[Á`È®tÃB¤½¦¡
- Cells(i, 3) = "Sub_Add": Cells(i + 1, 3) = "Sub_None": Cells(i + 2, 3) = "Total" '¼g¤J¼ÐÃD
- End Sub
½Æ»s¥N½X |
|