ªð¦^¦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)

¦^´_ 14# popomilk
Sub Test()
  Dim xR As Range
  
  Set xR = Range("A7")
  With xR
    If Trim$(.Value) = "Á`­p" Then
      .Offset(0, 1).Formula = "=SUMIF(A:A,""*¤p­p*"",B:B)"
      '.Offset(0, 1).FormulaR1C1 = "=SUMIF(C[-1],""*¤p­p*"",C)" '³o±ø»y¥y»P¤W¤@±ø»y¥y¦b³o¸Ì¬O¬Û¦Pªº®ÄªG¡A¦ý¤£¦Pªº¦ì¸m¤½¦¡¤£¤@¼Ë¡A©Ò¥H¤W­±¨º±ø¤ñ¸û¦n¥Î¡C
    End If
  End With
End Sub
¥@¬É¨º»ò¤j¡A¥i§Ú·Q¥h­þ¡H

TOP

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

«D±`ÁÂÁÂ
§Ú³£§Ñ°O¦³³o¤è¦¡¤F!
·PÁ±zªº¤À¨É

TOP

¦^´_ 14# popomilk


   
xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"¡@
³o­Ó¬O¥ý¼g¤½¦¡¡D¢Ð¢±¢±¡G=SUMIF(A$1:A21,"*¤p­p*",B$1:B21)
¦A¥H¡e¿ý»s¡f¨ú±oµ{¦¡½Xªº¡I

TOP

¦^´_ 13# Joforn
·PÁÂJofornªºªá®É¶¡À°§Ú


·Q¦A½Ð±Ð±z¬õ¦â³¡¤À
For I = 1 To EndRow   
½Ð°Ý³oÃä­n«ç»ò¼g¦¨For each
§Ú´«¦¨³o¼Ë¤@ª½Åã¥Ü¿ù»~
For Each I In Range([A1], EndRow)
³o¨âºØ§PÂ_¦¡¬Oºâ¬Û¦P¥\¯àªº¶Ü??

·PÁÂ

TOP

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

«D±`ÁÂÁ§Aªá®É¶¡¦^ÂÐ
¸g¹L§Aªº±Ð¾Ç¡A§Ú¯uªºÀ´¤F«Ü¦h!! (¼g¤F«Ü¦hµ§°O)Macro1 ¬O¥Î
¦bAÄæ¥Îfor eachªº¥Îªk¡A§ì¥X¤p­pªº¦æ¼Æ
¨Ã¥B¦bÁÙ¨S§ì¨ì¤p­p¤§«e ¤@ª½ÅýNÀHµÛ¦æ¼ÆN+1
¸I¨ì¤p­p®É ±Ò°Êsum ¥[Á`½d³ò¥Î1-Nªº¤è¦¡¸Ñ¨M  (²×©óµy·LÀ´sum¦bVBªº¥Îªk¤F¡A·P°Ê)
¥[Á`«á¡A¦A¥tN=0 ­«·s¶}©l




Macro3 ¬Û¸û©ómacro1ªº¤£¦P
À³¸Ó´N¦bµo²{AÄ檺½s¸¹³£¬O11¦r¤¸¡A
©Ò¥H±NxH = xR(1, 2)
¦b²Ä¤@¦¸¸I¨ìAÄ榳11¦r¤¸®É¥BN=0®É¡A³]©w¤@­ÓÅܼưO¿ý¸Ó¦æªºÀx¦s®æ¦ì¸m¡A¨ÃÅýN¬°1
³Ì«áªºN=1¬OÁקK¦A­«Âг]©wXH³o­ÓÅܼơAÅý¥L´N©T©w¦b²Ä¤@¦¸¸I¨ì11­Ó¦r¤¸®Éªº¨º¦æªºÀx¦s®æ
¦bsum¥[Á`«á¡A­«·sÅýN¬°0
¤§«á¸I¨ì11­Ó¦r¤¸®É¡A¦A­«·s³]©wXHÅܼÆ


ªá¤F¦n¦h®É¶¡¤F¸Ñ³o¨âªk¼gªºÅÞ¿è¤F
«D±`¨ØªA§AªºÁo©ú
µS¦p¤W¤F¤@°ó½Ò

³Ì«á·Q¸ò¸ò§A½Ð±Ð¼Ð¬õ¦rªº³¡¤À

If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"

¤@¯ëexcel
sumif³o­Ó¨ç¼Æ§Ú¬Oª¾¹Dªº
sumif(­n³Q§PÂ_ªº½d³ò°Ï¶¡¡A§PÂ_±ø¥ó,¥[Á`½d³ò)
§Ú¬Ý¤£¤ÓÀ´R1C[-1]
§Ú¬dºô¸ô¤W»¡¬O¬Û¹ï¦ì¸m
¦ý¬O¦pªG¦bexcelÀ³¸Ó¬O­n³]¸m¦¨ =SUMIF(A:A,"*¤p­p*",B:B)
¹ê¦b¬O¬Ý¤£À´ÂনVB»y¨¥«á¬O¥´¦¨³o¼Ë

³Â·Ð¦A½Ð¤j®v¼·¤¾¸Ñµª¤@¤U
«D±`·PÁÂ

TOP

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

TOP

Sub Macro3()
Dim xR As Range, N&, xH As Range
For Each xR In Range([A2], [A65536].End(3))
¡@¡@If xR Like "###########" And N = 0 Then Set xH = xR(1, 2): N = 1
¡@¡@If Trim(xR) = "¤p­p" Then
¡@¡@¡@¡@If N = 1 Then xR(1, 2) = "=SUM(" & Range(xH, xR(0, 2)).Address & ")":  N = 0
¡@¡@End If
¡@¡@If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"
Next
End Sub

³o¬O¥t¤@¤èªk¡A¥i¥H§óºë·Ç§ì¤p­p½d³ò¡A
·í¢ÏÄ榳¡e¸ê²£½s¸¹¡]¢°¢°½X¼Æ¦r¡^¡f®É¡A°O¿ý¬°¡e­º®æ¡f¦ì¸m(xH)¡A
·í¹J"¤p­p"®É¡A¡e­º®æ¡f¦Ü¡e¤W¥k¤@®æ¡f§Y¬°¤p­p½d³ò¡I¡I¡I

TOP

¦^´_ 10# popomilk


   
If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": xR(0, 3) = N: N = 0

xR(0, 3) = N  ·í¹J"¤p­p"®É, °O¿ýN­È, ³o¼ËÀ³¸û¥i²M·¡¨äµ{§Ç
¸I¨ì"¤p­p"®É, ¶×¾ã©Ò»Ý¤u§@, ¨Ã±NN­ÈÂk0, Ä~Äò¤U¤@°Ï¬q


"=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")"
&¥Î¨Ó³s±µ¤å¦r, ¥[¤W¦ì§}§Y¦¨¤@­Ó¤½¦¡ªº¤å¦r¦ê¡G=SUM($B$2:$B$4)

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 : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD