Worksheetsオブジェクト(エクセル)

質問

エクセルで次のような文を書いて実行したところ
Worksheets(sheet_name).Cells(r.Row, myFindR.Column).Value = "1"
実行時エラー9　インデックスが有効範囲にありませんとでました。
sheet_name=Left(Me.my_range, InStr(Me.my_range, "!") - 1)とRefEditで入力したセル範囲を指定しています。
rは、For Each r　In Range(Me.input_sheet)と同じくRefEditで入力したセル範囲から、コレクションを取得しています。
myFindRは、myFindR = Range(Me.my_range).Find(What:=r_r1.Value, lookat:=xlWhole)としています。
私の力では、なぜエラーが出るのか？解りません。
どなたか？詳しい方がいらっしゃいましたら教えてください。
よろしくお願い致します。

回答No.1

KenKen_SP · ベストアンサー

こんばんは。

コードはできるだけ全体象がわかるように掲載した方が良いですよ。文字数
制限ならこの回答の補足欄をご利用ください。

取り合えず、気になったとこです。

> Worksheets(sheet_name).Cells(r.Row, myFindR.Column).Value = "1"
> 実行時エラー9　インデックスが有効範囲にありませんとでました。

1.　sheet_name が正しく取得できていない
2.　myFindR が Nothing または不正値であるため、Column が取得できない

のいずれかに原因がありそうな気がします。

▼ 1. について

> sheet_name=Left(Me.my_range, InStr(Me.my_range, "!") - 1)

RefEdit はユーザーが手入力可能なコントロールです。つまり、
Sheet1!$A$1 のつもりで、Shee1!$A$1 などのように間違った入力した場合、
Range の参照でエラーになります。エラートラップした方が良いです。

　# Range からシートオブジェクトを参照するには Parent が便利です。

　　On Error Resume Next
　　' RefEdit の値が不正の場合 Range を参照しようとするとエラーが発生
　　’それをトラップします
　　sheet_name = Range(Me.my_range).Parent.Name
　　If Err Then
　　　　MsgBox "RefEdit の値が不正でエラー"
　　　　Exit Sub
　　End If
　　On Error GoTo 0

▼ 2. について

> myFindR = Range(Me.my_range).Find(What:=r_r1.Value, lookat:=xlWhole)
　　↓
Set myFindR = Range(Me.my_range).Find(What:=r_r1.Value, lookat:=xlWhole)

Set がないと変数 myFindR には Find で見つかったセルの値が代入されて
しまいそうな気がしますが...だとすれば、

> myFindR.Column

はエラーになりませんか？ 変数は宣言してますか？

変数を全然宣言しない、つまり全部 Variant でコードを書いていると、この
ようなミスが発見しにくくなるのですが...この点はどうでしょうか？

それから Find は該当セルがない場合 Nothing を返しますのでエラートラップ
をしてみて下さい。

Dim myFindR As Range
　　Set myFindR = Range(Me.my_range).Find(What:=r_r1.Value, _
　　　　　　　　　　　　　　　　　　　　　LookIn:=xlValues, _
　　　　　　　　　　　　　　　　　　　　　LookAt:=xlWhole)
　　If myFindR Is Nothing Then　'<--ココがエラートラップ
　　　　MsgBox "該当セルなし"
　　　　Exit Sub
　　End If

以上です。