エクセルＶＢＡで実行時エラー 91 が出ます

質問

エクセル2000です

各部署の棚卸を纏める為のＶＢＡを作成しているのですが、実行時にエラーになってしまいます
エラーメッセージは
「実行時エラー　91
　　オブジェクト変数またはＷｉｔｈブロック変数が設定されていません」

です

ご教授お願いいたします

Sub 棚卸()

Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = Worksheets("在庫集計票")
Set sh2 = Worksheets("棚卸表")

x = sh2.Range("A65536").End(xlUp).Row

Z = sh1.Range("d2").Value　’部署番号

sh1.Range(Cells(5, Z), Cells(3000, Z)).ClearContents

For i = 2 To x

y = sh1.Range("A2:A" & Range("A2").End(xlDown).Row). _
Find(sh2.Cells(i, "a")).Row　’ここでエラーが発生します

sh1.Cells(y, Z) = sh2.Cells(i, "c")

Next i

End Sub

回答No.5

Wendy02 · ベストアンサー

こんにちは。

まず、できるだけ、エラー番号は、その内容を書いてください。
91: オブジェクト変数または With ブロック変数が設定されていません。

それから、VBAやVBでは、大文字・小文字の違いはないのですが、なるべく、変数の小文字・大文字の区分けをしたほうが見やすいのではないかと思います。

なお、この内容は、本当に、以下のようなVBAが必要あるのか、ちょっと良くわかりません。関数で、かなり代用できる部分があれば、それを置き換えてしまってもよいと思います。

Sub 棚卸r()

　　Dim sh1 As Worksheet
　　Dim sh2 As Worksheet
　　Dim rng As Range
　　Dim x As Long, y As Long, i As Long, z As Integer
　　Dim rFind As Range
　　Set sh1 =Worksheets("在庫集計票")
　　Set sh2 =Worksheets("棚卸表")

　　x = sh2.Range("A65536").End(xlUp).Row
　　z = sh1.Range("D2").Value '部署番号

　　Application.ScreenUpdating = False
　　With sh1
　　　.Range(.Cells(5, z), .Cells(3000, z)).ClearContents
　　　Set rng = .Range("A2", .Range("A2").End(xlDown))
　　　If rng(rng.Count).Row = Rows.Count Then MsgBox "対象列は空です": Exit Sub
　　End With
　　For i = 2 To x
　　　　Set rFind = rng.Find(sh2.Cells(i, "A"))
　　　　If Not rFind Is Nothing Then
　　　　　 y = rFind.Row
　　　　　 sh1.Cells(y, z) = sh2.Cells(i, "C")
　　　　End If
　　Next i
　　Application.ScreenUpdating = True
End Sub

お礼

ありがとうございます

エラーメッセージは、本文の最初のほうに書かせていただきましたが、タイトル部につけたほうが分かりやすかったですよね

今後、気をつけます

エラーは出ませんでしたが、思うような動作をしてくれません
私の根本的な考え方にミスがあるのかと思います

もう一度，組立を考えるところから始めたいと思います

回答No.4

pkh4989

以下の方法では、どおですか？

With sh1.Range("A2:A" & Range("A2").End(xlDown).Row)
For i = 2 To x
　　Set y = .Find(sh2.Cells(i, "a").Row)
　　If y Is Nothing Then
　　　　'エラー処理
　　Else
　　　　 .Cells(y, Z) = sh2.Cells(i, "c")
　　End If
Next
End With

お礼

ありがとうございます

エラーは出ませんでしたが、思うような動作をしてくれません
私の根本的な考え方にミスがあるのかと思います

もう一度，組立を考えるところから始めたいと思います

回答No.3

taka0028

改行のせいではないですか？
Findの前に空白があるからでは？
y = sh1.Range("A2:A" _
& sh1.Range("A2").End(xlDown).Row).Find(sh2.Cells(i, "a")).Row
として見ればどおですか？

お礼

ありがとうございます
コピーさせていただきましたが、同じ現象でした

回答No.2

zap35

＃０１です。１行抜けていました。

With sh1
　Set rng = .Range("A2:A" & .Range("A2").End(xlDown).Row). _
　　　Find(sh2.Cells(i, "a"))
　If rng Is Nothing Then
　　　'エラー処理
　Else
　　　y = rng.Row
　　　.Cells(y, Z) = sh2.Cells(i, "c")
　End If
End With

お礼

ありがとうございます
エラー処理を組み込ませていただきました
が、まだ同じエラーが出ます

回答No.1

zap35

sh2がアクティブな状態で実行するとエラーになります。sh1がアクティブなら動くでしょう。

y = sh1.Range("A2:A" & sh1.Range("A2").End(xlDown).Row). _
Find(sh2.Cells(i, "a")).Row　
でエラーは消えると思います。

でもFindメソッドが失敗（Not Found)だったらまたエラーになりますよ。いきなり
　sh1.Cells(y, Z) = sh2.Cells(i, "c")
するまえにエラー判定処理を組み込むことをお勧めします

With sh1
　Set rng = .Range("A2:A" & .Range("A2").End(xlDown).Row). _
　　Find(sh2.Cells(i, "a"))
　If rng Is Nothing Then
　　'エラー処理
　Else
　　sh1.Cells(y, Z) = sh2.Cells(i, "c")
　End If
End With

お礼

すみません
お礼を書き込むべきところ、補足で入れてしまいました
ありがとうございました

補足

ありがとうございます
教えていただいたように変更し、更にsh1をアクティブな状態で実行しました
ですが、まだ、同じ箇所で同じエラーが出ます
何が原因なのでしょうか