エクセルVBAなぜ実行時エラーが？

質問

エクセル２０００です。
DATAと名づけた表の値を変換し、最大値から端数をプラマイするマクロなのですが、途中で「実行時エラー９１　オブジェクト変数またはWithブロック変数が設定されていません。」となってしまいます。
なぜ出るかわからないので別なBookに同じ名前のRange
をつくり同様の表でためしたらエラーにならずちゃんと作動します。
本番用のBookでのみエラーがでます。なぜなのでしょうか？
実行時エラーのでる
With Range("DATA").Find(mx, LookIn:=xlValues)
.Value = .Value + dff '
End With
を、Withブロックをつかわず
Range("DATA").Find(mx, LookIn:=xlValues).Select
で試しても本番のBookではエラーになります。ほんとに困っています。

Sub 調整()
Dim r As Double
Dim c As Range
Dim dff As Integer, mx As Long
r = 25000 / Range("初期").Value
With Sheets("内訳")
Range("DATA").Value = .Range("F57:L73").Value '初期値複写
'MsgBox "初期値転写完了"
For Each c In Range("DATA")
If c.Value <> "" Then
c.Value = Application.WorksheetFunction.Round(c.Value * r, -1)
End If
Next
'MsgBox "初期変換完了"
dff = 25000 - Range("変換後")
If dff <> 0 Then
'MsgBox dff
mx = Application.WorksheetFunction.Max(Range("DATA"))
'MsgBox mx
With Range("DATA").Find(mx, LookIn:=xlValues)
.Value = .Value + dff 'ここで実行時エラー!
End With
End If
End With
End Sub

回答No.5

Wendy02 · ベストアンサー

こんにちは。
Wendy02です。

いずれにしても、Find 自体が問題が発生しているとなると、一度、ワークシートのメニューから、検索を試してみることですね。

必ずしも、これが通っても、マクロで通るとは限りません。例としては、検索値を手で入れた場合ではうまくいくけれども、変数で入れた場合や、また、ワークシートの書式の違いによって、検索できないことがあります。Findは、その点がひじょうにややこしくて、一般の数字は問題ない「はず」ですが、いくつかトライアルをしてみるしかありません。

何か別のトラブルが含まれていて、それが表に出てきているかもしれません。

Find メソッドで行っているときと、実際の検索のシート・オブジェクトなどに整合性がなくなっている可能性もあります。それから、こういう場合に、私は、全体で試さずに、一部のコードを取り出して、スモールサンプルで、検査します。

例:

LookAt:=xlWhole　→　xlPart にしたら？
LookIn:=xlValues　→　xlFormulas　にしたら？

Sub Test()
Dim mx As Long

dff = 10
mx = Application.WorksheetFunction.Max(Range("DATA"))
　'Worksheets("内訳").Activate
　With Worksheets("内訳").Range("F100:L116").Find(What:=mx, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
　'.Select
　.Value = .Value + dff　 'ここで実行時エラー!
End With
End Sub

念のため、標準モジュールに書いていますよね。

お礼

なんと、驚いたことに、手動でメニューの「編集」、「検索」でやってみても、範囲内から数値を検索することが出来ませんでした！最大、最少にかかわらず、表内の数値はどれも検索できないのです。
ワークシート関数で=MAX(範囲)ではちゃんと出るのですが・・・。

これはどういうことなのでしょう？

回答No.7

Wendy02

merlionXX さん、Wendy02です。

とりあえず、解決してよかったです。
私は、たぶん、今日のことは、深く記憶に留めておくことにします。時間・日にちの検索は、マクロではうまくできないのは知っていたので、おもに、Match 関数を使っていましたが、「会計」もそうだったのですか。なんとなく、「書式」が関係していることがあるのは知っていたのですが、難しいですね。

お礼

ありがとうございました。
わたしも今日は印象に残る一日でした。

Wendy02さんのアドバイスがなかったら土日もなくなっていたところです。
有難うございました。

回答No.6

Wendy02

追伸です。

LookAt:=xlWholeは、そのままで、

Dim mx As Long
　　↓

Dim mx As Double

にしてみたら、いかがですか？

お礼

なんどもなんどもありがとうございます。

問題はマクロではなくシートだったようです。

表の書式を「会計」から「標準」に変えたところ、無事作動しました。

テスト用はもちうろん「標準」でした。本番用が「会計」だったのが原因のようです。とほほ。

回答No.4

Wendy02

merlionXX さん、こんにちは。

実際には、最初のご質問のコードでは、読み取れないのですが、もしかして、それは、Findを使うシートはActiveではないのではありませんか？(^^;
明示的に、シート名を入れても、シートを　Activate しないと、Findが通らないときがあります。それは、一種のバグのようなものだと思います。(違うかもしれませんが)

まだ、新しいコードは、別のブックで、Find が、最後まで一度も通っていませんね。

たぶん、一度、Find が通れば、Activeでなくても通るようなのですが、そういう偶然性(？)があるようなのです。

たぶん、Find の前に、検索させるシートを.Activeとか. Select とかすればよいのですが……。

ちょっとコードとして、格好が悪いですね。そういうようですと、Find 自体が失敗かもしれないなって思っています。一般論なのですが、Excelのワークシート上のコマンドを使用する時、その状況に左右されることがあり、必ずしもマクロではうまくいかないものがいくつかある、ということです。（私の配慮が足らなかったようで、申し訳ないです! ）

お礼

何度も何度もすみません、恐縮です。

わたしもテストではうまくいき、本番でだめ、別ファイルに転記した同一コードはうまくいき、再度本番がだめで、パニクってます。
アクティブじゃないせいかとも思い、本番シートをアクティブにして試してもみましたが同じなんです。参りました。

回答No.3

Wendy02

merlionXX さん、こんにちは。
Wendy02です。

後、私も良く失敗する例としては、Find の引数の省略による失敗です。

今回も、私は省略してしまいましたが、Find は、前回使った設定を残していることがあって、時々失敗しますので、私の　VB Editor には、Find のテンプレートをいれています。そうしないと、つい忘れてしまいます。

With Find ( _
What := mx , _
LookIn := xlValues , _
LookAt := xlWhole , _
SearchOrder:= xlByColumns , _
SearchDirection:= xlNext)

これは、一行にしてしまってよいです。通常、AfterとMatchCase MAtchByte は抜いてよいですが、人によっては、引数の名称を用いず、引数を全て入れる代わりに「,」だけで数字のみだけで済ませてしまう人もいます。ただ、それは、コツがあるようで、組み込み定数ではないようなので、私は詳しくありません。

お礼

何度もお手数をおかけします。

With .Range("F100:L116").Find(What:=mx, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
.Value = .Value + dff
End With

としてみましたが、やはり同じ.Value = .Value + dff
でエラーになってしまいます。

回答No.2

Wendy02

こんにちは。Wendy02です。

かなり、余談が含まれていますが、ご勘弁ください。

mx = Application.WorksheetFunction.Max(Range("DATA"))

Find の手前の値から見てみるとよいです。
領域に数値がある限りは、論理的に、エラーにはなるはずがありません。しかし、エラーになるというのは、mx 値が取れていないからです。

見つからない場合でも、必ず、値　0 が入りますね。mx 値は、Long型ですから、避けようがありません。それで、Find で探せば、見つからないので、エラーになりますね。

そこで、もう1つ気になっているのは、前回見て気が付いたのは、Range("DATA")ということです。

VBAでは、通常、そのような書き方はしません。その理由は、変えられてもコードからでは気が付かない、ということです。暗黙的な領域だということですね。もう1つは、名前定義というのは、VBA側からだと、意外に使いにくいという面があります。名前定義を使う場合は、VBAの場合、その都度削除しなければならない時があるくらいです。

ですから、最初から、領域のキメウチが出来ないときは、Range("A1").CurrentRegion などで、領域を確保するか、Application.InputBox で、対話型入力するか、どちらかだということになりますね。

お礼

いつもありがとうございます。

mx = Application.WorksheetFunction.Max(Range("DATA"))
MsgBox mx

でmxの値がちゃんと返るのでmx 値が取れているのではないでしょうか？

ご指摘のRange("DATA")をすべて、Range("F100:L116")に変えてみましたが同様でした。（泣）

回答No.1

ja7awu

まぁ、一番考えられることは、Findで見つかっていないことでしょうね。

エラーが出るデータで、　.Value = .Value + dff 　の行にブレークポイントを設定し、
コードを実行し、イミディエイトウィンドウに

? Range("DATA").Find(mx, LookIn:=xlValues).Address <Enter> や、

? Range("DATA").Find(mx, LookIn:=xlValues) Is Nothing <Enter>　で何と返りますか?

お礼

早速ありがとうございます。
大変申し訳ありませんが、イミディエイトウィンドウの使い方がわかりません。

.Value = .Value + dff 　の行にブレークポイントを設定するにはクリックして茶色い●を出せばいいのですよね？

? Range("DATA").Find(mx, LookIn:=xlValues).Address <Enter> は

? Range("DATA").Find(mx, LookIn:=xlValues).Addressをコピペしてエンターキーですか？

そうやったら実行時エラーの同じメッセージがでましたが・・・。（泣）

補足

今、Value = .Value + dff の行にブレークポイントを設定し、コードを実行し、イミディエイトウィンドウに

? Range("DATA").Find(mx, LookIn:=xlValues) Is Nothing とコピペしてエンターキーしたら
Trueと返りました。

xlValuesのところにカーソルがふれたらxlValues=-4163と表示されました。？？？