【VBA】"オブジェクトが必要です"メッセージ出力

質問

VBAを使用し、A列に日付、B列に数量、C列に単価、D列に金額を入力し、
数量*単価にて、金額を求めるVBAを作成しています。
そこまでは上手くいくのですが、D列で求めた金額を最終行で合計する事で
躓いてしまっています。
行は常に追加され可変の為、最終行を「Cells(Row.Count, 1).End(xlUp).Row」
にて引っ張ってこようと思っております。以下のようなVBAを記載しましたが、
「オブジェクトが必要です」とのメッセージがでて、処理が上手くいきません。
どのような問題があるのか、お分かりの方、ご回答頂けますと幸いです。

■環境
　Windows7
　Excel2010

■VBA
Sub test()
Dim i As Long
Dim j As Long
Dim k As Long

For i = 2 To Cells(Row.Count, 1).End(xlUp).Row
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
Next
j = Cells(Row.Count, 1).End(xlUp).Row + 1
k = Cells(j, 1).End(xlUp).Row
Cells(j, 4) = WorksheetFunction.Sum(Cells(2, 4), Cells(k, 4))

End Sub

回答No.2

kagakusuki

>「オブジェクトが必要です」とのメッセージがでて、処理が上手くいきません。

　Cells(行番号,列番号)の中で行番号を指定する所にRow.Countとしている事が原因です。
　Row.Countではなく

Rows.Count

です。

　後、B列やC列の途中に文字列等の数値以外の値が入力されているセルが存在していると、そこでエラーとなってマクロが途中で停止してしまいますから、

For i = 2 To Cells(Row.Count, 1).End(xlUp).Row
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
Next

の途中にIF文を加えて

For i = 2 To Cells(RowS.Count, 1).End(xlUp).Row
If IsNumeric(Cells(i, 2)) And IsNumeric(Cells(i, 3)) And Cells(i, 2).Value <> "" And Cells(i, 3).Value <> "" Then
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
Else
Cells(i, 3).ClearContents
End If
Next i

などの様にされた方が良いと思います。

　後、御質問の件の場合は、処理の前後でA列の最終行が変化する訳ではないのですから、

For i = 2 To Cells(Row.Count, 1).End(xlUp).Row

の所と

j = Cells(Row.Count, 1).End(xlUp).Row + 1

の所の2箇所で

Cells(Row.Count, 1).End(xlUp).Row

という同じ処理を行っているのは2度手間ですし、

Cells(j, 1).End(xlUp).Row

の値は、毎回必ず

Cells(Row.Count, 1).End(xlUp).Row

と同じ値になるのですから、

k = Cells(j, 1).End(xlUp).Row

としているのは2度手間どころか3度手間で、無駄に処理を多くしています。
　ですから、

For i = 2 To Cells(Row.Count, 1).End(xlUp).Row

の所を

k = Cells(RowS.Count, 1).End(xlUp).Row
For i = 2 To k

としておく事で、先にkの値を求めてから、そのkの値を使ってFor～Nextの最後のiの値を決める様にした方が良いと思います。
　そして、jの値は単にkよりも1つ多いだけなのですから、Cells(j, 4) を指定する際にも変数jは使わずに、

Cells(k+ 1, 4)

とするだけで事足ります。

　それから、例えば元データが2行目～99行目に存在していた場合、D100セルにはD2:D99のセル範囲の総計を入力しなければならない筈ですが、

Sum(Cells(2, 4), Cells(k, 4))

としたのでは、

SUM(D2:D99)

ではなく、

SUM(D2,D99)

という意味になってしまい、単にD2とD99という2つのセルの値を足しただけの

D2+D99

と同じ値にしかなりません。
　ですから、

Sum(Cells(2, 4), Cells(k, 4))

ではなく

Sum(Range(Cells(2, 4), Cells(k, 4)))

として下さい。

　以上の点を修正すると次の様なものになります。

Sub test改()

Dim i As Long
Dim k As Long

k = Cells(RowS.Count, 1).End(xlUp).Row
For i = 2 To k
If IsNumeric(Cells(i, 2)) And IsNumeric(Cells(i, 3)) And Cells(i, 2).Value <> "" And Cells(i, 3).Value <> "" Then
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
Else
Cells(i, 3).ClearContents
End If
Next i
Cells(k + 1, 4) = WorksheetFunction.Sum(Range(Cells(2, 4), Cells(k, 4)))

End Sub

　後それから、一定のセル範囲に対して同じ処理を行う場合には、For i = ～Next iの様な変数iの値を基準に繰り返し処理を行うという方法の他にも、指定したセル範囲自体に対して逐次繰り返し処理を行うFor Each ～ In　～　Nextを使うという方法もあります。

Sub QNo8981137_オブジェクトが必要ですメッセージ出力()

Dim c As Range
Dim myCell As Range
For Each c In Range(Cells(2, 1), Cells(RowS.Count, 1).End(xlUp))
Set myCell = c.Offset(0, 3)
If IsNumeric(c.Offset(0, 1)) And IsNumeric(c.Offset(0, 2)) And c.Offset(0, 1).Value <> "" And c.Offset(0, 2).Value <> "" Then
myCell.Value = c.Offset(0, 1).Value * c.Offset(0, 2).Value
Else
myCell.ClearContents
End If
Next c
myCell.Offset(1, -3).Value = "合計"
myCell.Offset(1).Value = WorksheetFunction.Sum(Range(Cells(2, 4), myCell))

End Sub

【参考URL】
　よねさんのWordとExcelの小部屋 > Excel VBA入門 > 7.繰り返し処理(For～Next、Do～Loop) > For Each In～Next
　　http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_for_next.html#each

回答No.1

noname#212067

どっかから、コピーしたり、適当に手直ししても動きません
動かないのはオブジェクト指向のプログラミングを理解していない証拠です
時間が有り余って暇つぶしをしているのなら良いですが
無理にVBAを使わないでシート関数を使ったほうが、無駄な時間を使わないで良いと思います