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

[µo°Ý] VBA¦Û°Ê¥[Á`

[µo°Ý] VBA¦Û°Ê¥[Á`

¤p­p³¡¤À¹q¸£²£¥Xªº³£¨S¦³¥[Á`¤½¦¡
·Q¸ÕµÛ¥Î¥¨¶°¥Î¬Û¹ï¦ì¸m¼ÒÀÀ¼gµ{¦¡
¦ý±o¥X¨Óªº³£¬O³oºØ
  ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:RC)"
¦n¹³¨S¿ìªk¥Î

½Ð°Ý¦³«e½ú¯à±Ð¾Ç¶Ü
·PÁÂ

¥[Á`.rar (7.81 KB)

  1. Sub Test()
  2.   Dim I           As Long
  3.   Dim EndRow      As Long
  4.   Dim Ranges      As Range
  5.   Dim Range1      As Range
  6.   Dim strFormula  As String
  7.   
  8.   EndRow = Range("A" & Rows.Count).End(xlUp).Row
  9.   For I = 1 To EndRow
  10.     With Range("A" & I)
  11.       Select Case Trim$(.Value)
  12.         Case "¤p­p"
  13.           .Offset(0, 1).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
  14.           If Ranges Is Nothing Then
  15.             Set Ranges = .Offset(0, 1)
  16.           Else
  17.             Set Ranges = Union(Ranges, .Offset(0, 1))
  18.           End If
  19.         Case "Á`­p"
  20.           If Not Ranges Is Nothing Then
  21.             strFormula = vbNullString
  22.             For Each Range1 In Ranges
  23.               With Range1
  24.                 If Len(strFormula) Then
  25.                   strFormula = strFormula & "," & .Address
  26.                 Else
  27.                   strFormula = .Address
  28.                 End If
  29.               End With
  30.             Next Range1
  31.             Set Ranges = Nothing
  32.             .Offset(0, 1).Formula = "=SUM(" & strFormula & ")"
  33.           End If
  34.       End Select
  35.     End With
  36.   Next
  37. End Sub
½Æ»s¥N½X
¥@¬É¨º»ò¤j¡A¥i§Ú·Q¥h­þ¡H

TOP

¦^´_ 2# Joforn


  Joforn¹ê¦b¤Ó¯«¤F
½Ð®e³\§Ú¦b¾Ç²ß¤§«e Åý§Ú¥ý½¤«ô±z
«D±`·PÁÂ

TOP

¦^´_ 2# Joforn


¤j®v½Ð°Ý¦pªG
¨C­Ó¶µ¥Øªº¼Æ¶q¤£©T©wªº¸Ü
­n«ç»ò­×§ïµ{¦¡½X©O

·PÁÂ!!

¥[Á`2.rar (8.61 KB)

TOP

¦^´_ 4# popomilk

¦³¿ìªk¦Û°Ê¼g¤J°»´ú¸Ó¶µ¥Ø­n¥[Á`¼Æ¶q¦³´X­Óªº¤èªk¶Ü??
¦Ó¤£¬O
¸ê²£½s¸¹¤@¶}ÀY¶µ¥Ø¦³¨â­Ó=SUM(R[-2]C:R[-1]C)"
¸ê²£½s¸¹¤G¶}ÀY¶µ¥Ø¦³¤­­Ó=SUM(R[-5]C:R[-1]C)
¸ê²£½s¸¹¤T¶}ÀY¶µ¥Ø¦³¤T­Ó=SUM(R[-3]C:R[-1]C)""

«D±`·PÁÂ

TOP

  1. Sub Macro1()
  2. Dim xR As Range, N&
  3. For Each xR In Range([A2], [A65536].End(3))
  4.     If Trim(xR) = "¤p­p" Then
  5.        If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": N = 0
  6.        If N = 0 Then GoTo 101
  7.     End If
  8.     N = N + 1
  9.     If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"
  10. 101: Next
  11. End Sub
½Æ»s¥N½X
  1. Sub Macro2()
  2. Dim xR As Range, N&
  3. For Each xR In Range([A2], [A65536].End(3))
  4.     If Trim(xR) = "¤p­p" Then xR(1, 2) = _
  5.         "=SUM(R1C:R[-1]C)-SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)*2"
  6.     If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"
  7. Next
  8. End Sub
½Æ»s¥N½X
¨âºØ¤èªk!!!

TOP

¤èªk¤Gªº¤½¦¡:
¤p­p:=SUM(B$1:B4)-SUMIF(A$1:A4,"*¤p­p*",B$1:B4)*2
Á`­p:=SUMIF(A$1:A21,"*¤p­p*",B$1:B21)

TOP

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

ª©¥D¤Ó¯«©Ô¡A¯«¤â
¦ý§Ú¹ê¤O¤Ó®z¡A¦³¨Ç¬Ý¤£À´¡A¦V±z½Ð±Ð
1.
For each xR In Range([A2], [A65536].End(3))
½Ð°Ý¨º­ÓRange([A2]], [A65536].End(3))¬O¤°»ò·N«ä©O??
§Úª¾¹D[A65536].End(3)¬OAÄæ±q³Ì¤U­±¤W¨Ó²Ä¤@­Ó«DªÅ¥ÕªºÀx¦s®æ

2.
   If Trim(xR) = "¤p­p" Then
   If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": N = 0
  
½Ð°Ýxr(1,2)¬O¤°»ò·N«ä??

  «D±`·PÁÂ!!

TOP

¦^´_ 8# popomilk


   
1.
For each xR In Range([A2], [A65536].End(3))
½Ð°Ý¨º­ÓRange([A2]], [A65536].End(3))¬O¤°»ò·N«ä©O??
§Úª¾¹D[A65536].End(3)¬OAÄæ±q³Ì¤U­±¤W¨Ó²Ä¤@­Ó«DªÅ¥ÕªºÀx¦s®æ
¡Ä¢Ï¢±¨ì¢ÏÄæ³Ì«á¤@­Ó«DªÅ®æªº½d³ò¡@

2.
   If Trim(xR) = "¤p­p" Then
   If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": N = 0
½Ð°Ýxr(1,2)¬O¤°»ò·N«ä??
¡ÄxR(1, 2) ¡× xR.Cells(1, 2)¡A¬Û¦P·N«ä¡@

TOP

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


ÁÂÁ§Aªº¸ÑÄÀ  §Ú¬ÝÀ´¤F~~¤Ó°ª¿³¤F (§Ú¬ã¨s¤F¦n¤[)
¦ý±µ¤U¨Ó«á­±ÁÙ¬O¬Ý¤£À´
¨º½Ð°Ý¼Ð¬õ¦âªº¬O¤°»ò·N«äªü?? ¨D¯«¤â¦A«×¶}¥Ü
  If Trim(xR) = "¤p­p" Then
If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": N = 0
§Ú²{¦b¤j·§ª¾¹Dªº¬O³o¼Ë¤l
·í¤@¶}©lN¼Æ¨ì4®É (A2-->A5)
¥Ñ©óA5¬O¤p­p
¦]¦¹·íN=4®É·|±Ò°Ê  If Trim(xR) = "¤p­p" Then


xR(0,2)¬O±qA5©¹¤W²¾¤@®æ¡A¨Ã©¹¥k²¾¤@®æ¡A
xR(0,2)=B4
xR(1 - N, 2)¡A·íN¬°4-->xR(-3,2)
xR(-3,2)=¬O±qA5­n©¹¤W²¾¥|®æ¡A¨Ã©¹¥k²¾¤@®æ
xR(-3,2)=B1
Range(xR(0, 2), xR(1 - N, 2)).Address==>$B$1¨ì$B$4ªº½d³ò


³Ì«áªº:N=0¦ü¥G¬O ÅýN­«·sÂk¹sªº¼Ë¤l?
½Ð°Ý§Ú¦b¼ÆNªº®É­Ô³£¬O«öF8¬ÝµÛµ{¦¡¤@­Ó¤@­Ó¼Æ¡A¦³¤°»ò¤èªk¯à°¨¤W¬Ý¨ì²{¦bN¶]¨ì¦h¤Ö¤F¶Ü??



«D±`ÁÂÁ±z¼·¤¾±Ð¾Ç

¹Ï¤ù 003.png (5.05 KB)

¹Ï¤ù 003.png

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD