准提部林

回復 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  當遇"小計"時, 記錄N值, 這樣應較可清楚其程序
碰到"小計"時, 匯整所需工作, 並將N值歸0, 繼續下一區段


"=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")"
&用來連接文字, 加上位址即成一個公式的文字串：=SUM($B$2:$B$4)

准提部林

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) = "小計" Then
　　　　If N = 1 Then xR(1, 2) = "=SUM(" & Range(xH, xR(0, 2)).Address & ")":  N = 0
　　End If
　　If Trim(xR) = "總計" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*小計*"",R1C:R[-1]C)"
Next
End Sub

這是另一方法，可以更精準抓小計範圍，
當Ａ欄有〔資產編號（１１碼數字）〕時，記錄為〔首格〕位置(xH)，
當遇"小計"時，〔首格〕至〔上右一格〕即為小計範圍！！！

Joforn

回復 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 "小計"
    
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 "總計"
    
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

複製代碼

popomilk

回復 12# 准提部林

非常謝謝你花時間回覆
經過你的教學，我真的懂了很多!! (寫了很多筆記)Macro1 是用
在A欄用for each的用法，抓出小計的行數
並且在還沒抓到小計之前 一直讓N隨著行數N+1
碰到小計時 啟動sum 加總範圍用1-N的方式解決  (終於稍微懂sum在VB的用法了，感動)
加總後，再另N=0 重新開始




Macro3 相較於macro1的不同
應該就在發現A欄的編號都是11字元，
所以將xH = xR(1, 2)
在第一次碰到A欄有11字元時且N=0時，設定一個變數記錄該行的儲存格位置，並讓N為1
最後的N=1是避免再重覆設定XH這個變數，讓他就固定在第一次碰到11個字元時的那行的儲存格
在sum加總後，重新讓N為0
之後碰到11個字元時，再重新設定XH變數


花了好多時間了解這兩法寫的邏輯了
非常佩服你的聰明
猶如上了一堂課

最後想跟跟你請教標紅字的部分

If Trim(xR) = "總計" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*小計*"",R1C:R[-1]C)"

一般excel
sumif這個函數我是知道的
sumif(要被判斷的範圍區間，判斷條件,加總範圍)
我看不太懂R1C[-1]
我查網路上說是相對位置
但是如果在excel應該是要設置成 =SUMIF(A:A,"*小計*",B:B)
實在是看不懂轉成VB語言後是打成這樣

麻煩再請大師撥冗解答一下
非常感謝

popomilk

回復 13# Joforn
感謝Joforn的花時間幫我


想再請教您紅色部分
For I = 1 To EndRow   
請問這邊要怎麼寫成For each
我換成這樣一直顯示錯誤
For Each I In Range([A1], EndRow)
這兩種判斷式是算相同功能的嗎??

感謝

准提部林

回復 14# popomilk


   
xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*小計*"",R1C:R[-1]C)"　
這個是先寫公式．Ｂ２２：=SUMIF(A$1:A21,"*小計*",B$1:B21)
再以〔錄製〕取得程式碼的！

popomilk

回復 16# 准提部林

非常謝謝
我都忘記有這方式了!
感謝您的分享

Joforn

回復 14# popomilk
Sub Test()
  Dim xR As Range
  
  Set xR = Range("A7")
  With xR
    If Trim$(.Value) = "總計" Then
      .Offset(0, 1).Formula = "=SUMIF(A:A,""*小計*"",B:B)"
      '.Offset(0, 1).FormulaR1C1 = "=SUMIF(C[-1],""*小計*"",C)" '這條語句與上一條語句在這裡是相同的效果，但不同的位置公式不一樣，所以上面那條比較好用。
    End If
  End With
End Sub