VBA/シート名の指定時に変数を使いたい

質問

タイトルがわかりにくく申し訳ありません。エクセルVBAについての質問です。

=================================
Dim myRng, fnd as Range

Set myRng = Sheets("Sheet1").Range("A1:A10")　//A1～10セルには野菜名が入っているとします。

For Each fnd In myRng.Areas

　　　※(省略)Sheet2からfndの値を検索し、見つけたらその行をコピーする記述をしています。

　　　Sheets("野菜_"& fnd.value).Range("A1").Pastespecial.........//★(問題の行です)
Next
=================================

（※部分は問題なく動作しているため省略させていただきました。）
やりたいことはSheet2でコピーした内容を別シートへ貼り付ける動作です。
別シート貼り付けの際、貼り付け先シートが複数存在しますので、シート名を指定したうえでペーストする記述をしたいですのですが、うまくいきません。

例えば　変数fndの値が"トマト"だとします。
貼付先シート名には”野菜_トマト”、”野菜_きゅうり”、”野菜_なす”　など複数ありますので、”野菜_”以下にfndの値を加えて貼付先シートを選択したいのです。

しかしながら上の記述ですと、「型が一致しません」「インデックスが有効範囲にありません」などのエラーが発生してしまいます。fnd.valueの部分がよろしくないのだと思うのですが、.valueを外したり、.textに置き換えてみたり、キャストなど試してみたがいずれも失敗に終わりました。

初歩的な質問でお恥ずかしい限りですが、★部分をどのようにするとシート選択がうまくできるかを教えていただけないでしょうか。
拙い説明で申し訳ありません。どうぞよろしくお願いいたします。

回答No.1

neKo_quatre · ベストアンサー

For Eachの指定で、
×：For Each fnd In myRng.Areas
○：For Each fnd In myRng

で良いのでは？
実際、エリアは複数領域でなくて１つですし。

--
For Each ～ Nextの中身を、

For Each ～
　MsgBox("!")
Next

とかってしてみると、ループの回数が期待される10回でないって確認できたりするかも。

お礼

.Areasは全く盲点でした。これを外しただけで解決できました。驚きの一言です。一人では見当違いなことばかり試していたので本当に助かりました。皆様ベストアンサーにさせていただきたいのですが、初心者目線で一番簡潔でわかりやすかったneKo_quatre様を選ばせていただきます。ありがとうございました！！

回答No.3

dogs_cats

その方法でも問題なく実行出来ますが。
fnd.Valueにトマトが格納されているか、デバックしていますか？
http://www.excel-excel.com/vbachair/step1-7.html

仮にsheet1のA1の値をsheet2A列で検索しその行の１０列分のデータを指定シートにコピーするコード化してみましたのでご参考まで。

Psr_rngのように変数格納しても構わないかと思います。お好きに方を選択下さい。

値だけの転記であれば書きのようにすれば値だけ転記出来ます。
左右のセル数が同数になるようにセル範囲を指定する必要があります。
Pst_rng .value=CP_rng.value

書式も必要なコピーは.Copy Destinationの方が処理が高速化出来ますのでお勧めです。
CP_rng.Copy Destination:=Pst_rng

Sub test()

Dim fnd As Range
Dim Fcell As Range
Dim Myrng As Range
Dim CP_Rng, Pst_rang As Range

Set fnd = sheets(("sheet1").Range("A1")

With Sheets("sheet2")

Set Myrng = Cells.Find(What:=fnd.Value, After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)

If Not Myrng Is Nothing Then

Set CP_Rng = .Range(Cells(Myrng.Row, 1), Cells(Myrng.Row, 10))
'Set Pst_rng = Sheets("野菜" & fnd.Value).Range("A1:J1")

CP_Rng.Copy
Sheets("野菜" & fnd.Value).Range("A1").PasteSpecial
End If

End With

End Sub

お礼

このような方法もあるのですね。とても勉強になります。今後の参考にもさせていただきます。ご丁寧にコードを教えていただきありがとうございます。感謝申し上げます。

回答No.2

eden3616

「Areas」は複数のセル範囲から該当のセル範囲を指定する場合に使用します。
例えば、A、B、Cの各1～5行目から、2番目のセル範囲を取得する場合、
以下のようにすればイミディエイトに「$B$1:$B$5」が表示されます。

Sub sample()
Debug.Print Range("A1:A5,B1:B5,C1:C5").Areas(2).Address
End Sub

該当のコードを修正する場合、以下のようになります。

For Each fnd In myRng.Areas
　　　↓

For Each fnd In myRng.Areas(1)

　　又は

For Each fnd In myRng

前者は、全てのセル範囲「A1:A10」の内、1番目のコレクションであるセル範囲「A1:A10」をセル範囲とする。
（対象のセル範囲が１つしかないので意味がない）

後者は、セル範囲「A1:A10」をセル範囲として指定する。
（対象のセル範囲が１つしかないのであればこれで良い）

お礼

大変勉強になりました。こちらの方法で無事解決できました。早々にご回答くださり感謝申し上げます。