[attach]32839[/attach]

[attach]32841[/attach]

[attach]32840[/attach]

[B65].FormulaArray = "=IF(MAX((COUNTIF(\$B1:B1,""<>"")=COUNTIF(OFFSET(\$B1,,,,COLUMN(\$A:\$AW)),""<>""))*(SMALL(IF(\$A3:\$A60=""小計"",ROW(3:60)),COUNTIF(\$B1:B1,""<>""))=ROW(3:60))*\$B3:\$AX60)=N(OFFSET(\$A1,SMALL(IF(\$A3:\$A60=""小計"",ROW(3:60)),COUNTIF(\$B1:B1,""<>""))-1,COLUMN(A1))),OFFSET(\$A1,SMALL(IF(\$A3:\$A60=""小計"",ROW(3:60)),COUNTIF(\$B1:B1,""<>""))-1,COLUMN(A1)),"""")"

PS︰列147程式碼目前點綠沒有執行。

[attach]32843[/attach]

[attach]32844[/attach]
[attach]32845[/attach]
[attach]32846[/attach]

EX : 目前提供的13個測試檔，執行後會在第6個(11-20)和第11個(12-08)各產生一次偵錯(如附件圖片)

PS︰目前13個測試檔的操作細況說明

N = 0: Cx = 0  '換檔後, 變數要歸零
For Each xR In [A2:A60]
If xR Like "*_*" Then N = N + 1
If xR = "小計" Then
S1 = xR(1, Cx + 2).Resize(1, N).Address
S2 = xR(1, Cx + 2).Address(0, 0)
Cells(65, Cx + 2).Resize(1, N) = "=IF(MAX(" & S1 & ")=" & S2 & "," & S2 & ","""")"
Cx = Cx + N: N = 0
End If
Next

[attach]32868[/attach]

[attach]32867[/attach]

N = 0: Cx = 0  '換檔後, 變數要歸零
dim xE as range
set xE=[A:A].find("總計",lookat:=xlwhole)
For Each xR In range("A2:A" & xE.row-2)
If xR Like "*_*" Then N = N + 1
If xR = "小計" Then
S1 = xR(1, Cx + 2).Resize(1, N).Address
S2 = xR(1, Cx + 2).Address(0, 0)
Cells(65, Cx + 2).Resize(1, N) = "=IF(MAX(" & S1 & ")=" & S2 & "," & S2 & ","""")"
Cx = Cx + N: N = 0
End If
Next

 歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)