[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Application.WorksheetFunction.sumで構文エラー』(VBA初心者)
シートに下記を数値及び文字列、式を記載しています
B E 1 Time Time(sum) 2 00:16:05 00:00:00 3 00:09:28 00:16:05 4 00:04:53 00:25:33 5 00:07:58 00:30:26 6 00:10:26 00:38:24 7 00:48:50
E列にB列の合計を計算したいので式を挿入
E2: 00:00:00
E3: sum($B$2:B2)
E4: sum($B2$2:B3)
以下式E7までをコピペ
>----------------------
ワークシート関数のSUMで上手く処理できたので
VBAで処理したいので以下のコードを書きましたが
Application.WorksheetFunction.sumで構文エラーがでます。
間違いの修正をお願いします。
Sub Time_SUM()
Dim Last As Long
Dim i As Long
Dim Ws1 As Worksheet
Set Ws1 = Worksheets("Time_SUM")
Last = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
Ws1.Range("E2") = "00:00:00"
For i = 3 To Last+1
ws1.cells(i,"E")=Application.WorksheetFunction.sum(ws1.Range("$B$2"):ws1.cells(i-1,"B"))
Next
Set Ws1 = Nothing
End Sub
< 使用 アプリ:Excel2021(WIN)、使用 OS:Windows 11 x64 >
ws1.Cells(i, "E") = Application.WorksheetFunction.Sum(ws1.Range("$B$2", ws1.Cells(i - 1, "B"))) とか ws1.Cells(i, "E") = Application.WorksheetFunction.Sum(ws1.Range("$B$2:$B" & i - 1)) ですかね。
(γ) 2022/01/13(木) 06:41
参考に Sub Time_SUM() Dim i As Long With Worksheets("Time_SUM") For i = 3 To .Cells(Rows.Count, "A").End(xlUp).Row + 1 .Cells(i, "E").Value = Application.WorksheetFunction.Sum(.Range("B2:B" & i - 1)) Next End With End Sub Sub Time_SUM数式編() Dim i As Long Dim Last As Long With Worksheets("Time_SUM") Last = .Cells(Rows.Count, "A").End(xlUp).Row + 1 .Range("E3:E" & Last).Formula = "=SUM($B$2:B2)" End With End Sub
(ピンク) 2022/01/13(木) 08:34
(VBA初心者) 2022/01/13(木) 09:36
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.