【エクセルVBA】「インデックスが有効範囲にありません」というエラーがでます

質問

こんにちは。VBA初心者ですが、下記コードを実行すると
エラーが出てしまいました。

シート（シート1）のセルから別シート（シート2）のセルに
値のみコピーするVBAを組んでいます。
適宜、シート1に入力したものを読みこませていきたいので
変数を使用しています。

-------------------------------------------------------------
Sub セルのコピー()

Dim X As Integer

X = 3
Y = 2
Do While Cells(X, "A").Value <> ""
Sheets("シート1").Cells(X, "A").Copy

Sheets("シート2").Activate
Sheets("シート2").Cells(Y, "F").PasteSpecial _　Paste:=xlPasteValues　⇒エラー対象
Application.CutCopyMode = False

X = X + 1
Y = Y + 1

Loop

End Sub

------------------------------------------------------------
つまり、シート1のA3セルを先頭にA4,A5,A6・・・と続くセルの値
をシート2のF2を先頭としたセル（以下、F3,F4・・・）に値のみコピー
していきたいのですが。。

実行すると「インデックスが有効範囲にありません」というエラーが
でます。デバック対象は上記、「⇒エラー対象」の構文です。

変数の設定の仕方がおかしいのでしょうか。。
ご教示のほどよろしくお願いいたします。

回答No.3

KonnaMonde · ベストアンサー

>Sheets("シート2").Cells(Y, "F").PasteSpecial _　Paste:=xlPasteValues　⇒エラー対象
で、「PasteSpecial _　Paste:=xlPasteValues」　の　“_”　を消去してください。
また、
　Sheets("シート2").Activate　で　"シート2"　を　Activate　にすると、 Do　Loop　の頭に返ってきたとき、
　Do While Cells(X, "A").Value <> ""　の　Cells(X, "A")　は　"シート2"　の　Cells(X, "A")　を見に行きますので、　思っているような動作はしません。
　最後の　Loop の前に、　Sheets("シート1").Activate　を入れるか、While　の後の　Cells(X, "A").Value　の頭に、Sheets("シート1").　をつければ、この問題を回避することができます。

コピー先のシートは、アクティブにしなくても、ペーストする方法があります。

あなたのコードを少し変更します。

　Sub セルのコピー2()
　　　Dim X As Integer, Y As Integer

　　　X = 3
　　　Y = 2
　　　Do While Cells(X, "A").Value <> ""
　　　　　Sheets("シート1").Cells(X, "A").Copy Destination:=Sheets("シート2").Cells(Y, "F")
　　　　　X = X + 1
　　　　　Y = Y + 1
　　　Loop
　　　Application.CutCopyMode = False
　End Sub

注) 使っているブラウザの見る条件によって、「Sheets("シート1").Cells(X, "A").Copy Destination:=Sheets("シート2").Cells(Y, "F")」と「Destination:=Sheets("シート2").Cells(Y, "F")」が２行になったように見えることがあるかもしれません。　２つのコードはスペースを1つはさみ、続けて書いてください。

変更したコードの場合、"シート1" が常時表示されたままになります。

行数が２００行くらいとのこと、速度を早くしたい場合は、最初に
　application.ScreenUpdating=False
最後に、
　application.ScreenUpdating=True
を加えます。

なお、シート名は "Sheet1" ですか、"シート1"ですか、どちらかに揃えましょう。　、"シート1"と、"シート2"がブックの先頭から順になっているときは、シート名を使わなくてそれぞれ　Sheets(1)、Sheets(2)　でもいけます。　(1)、(2)　は左から　１番目のシート、２番目のシートという意味です。

試してみてください。

お礼

>konnamondeさま

ご回答ありがとうございます。
返信がおそくなり申し訳ありません。

シート名を誤転記していたのと、Do while cells(X,"A")の前にsheets("シート1")を追加、sheets("シート2").activateを削除したら、無事動作しました。

また、ScreenUpdatingプロパティを加えてみたら各段に動作が速くなり
感動しております。。

Sheets("シート2").Activate　で　"シート2"　を　Activate　にすると、 Do　Loop　の頭に返ってきたとき（以下省略）
⇒なるほど！でした。動作イメージがしっかり分かってないと。。ですね。

シート名を使わなくてそれぞれ　Sheets(1)、Sheets(2)　でもいけます
⇒こちらの方が使いやすそうですね。取り入れてみます！

回答No.4

Wendy02

こんばんは。

ご披露なさったコードは、Excel VBAでは、ふつうは書かない練習コードですから、それ自体にとやかくいっても、あまり身につかないように思います。

>実行すると「インデックスが有効範囲にありません」というエラーがでます。

このエラーは、コードからすると「シート名が違っている」という意味のはずです。
「シート2」というシート名をわざわざ付けているのでしょうか？全角・半角の間違いとかありませんか？

#1さん、#2さん,#3さんのご指摘は、ともに正しいです。
下に伸びるものは、Integer 型ではなく、Long型にしますが、

Dim x As Long
Dim y As Long

　Sheets("シート2").Cells(Y, "F").PasteSpecial Paste:=xlPasteValues　'アンダーライン余分

しかし、このコード自体は、シート1モジュールに書けば通るはずです。

標準モジュールに書けば、本来は、シート1 のA列を検査するはずが、シート2に移っていますので、シート2のA列が空なら、そこで止まります。

標準モジュールに書くと、アクティブシートが、
Sheets("シート2").Activate　'←これでシート2 に移ってしまっています。
そして、 Cells(X, "A").Value <> ""
Shhets("シート2") のA列を調べることになります。

ただ、あまり、失敗やエラーを出しやすい部分が多すぎるということは、自己流かもしれません。きちんとした教本で勉強したり、参考にしたりしてパターンを覚えたほうがよいのではないでしょうか？

以下も、あまり書かない方法ですが、End プロパティと、Copy メソッドを分かりやすくしたのコードです。

Sub Test2() '←なるべく２バイト文字は使わないほうがよいです。
　　Worksheets("Sheet1").Activate
　　Range("A3", Range("A65536").End(xlUp)).Copy Worksheets("Sheet2").Range("F2")
　　Worksheets("Sheet2").Activate
End Sub

Sub Test3()
　　Worksheets("Sheet1").Activate
　　Range("A3", Range("A65536").End(xlUp)).Copy
　　Worksheets("Sheet2").Range("F2").PasteSpecial Paste:=xlPasteValues
　　Worksheets("Sheet2").Activate
End Sub

お礼

>Wendy02さま

ご回答ありがとうございます。
返信が遅くなり申し訳ありません。

シート名を誤転記していたのと、Do while cells(X,"A")の前にsheets("シート1")を追加、sheets("シート2").activateを削除したら、無事動作しました。

このエラーは、コードからすると「シート名が違っている」という意味のはずです。
⇒それが分かりませんでした。。勉強になります。

ただ、あまり、失敗やエラーを出しやすい部分が多すぎるということは、自己流かもしれません。きちんとした教本で勉強したり、参考にしたりしてパターンを覚えたほうがよいのではないでしょうか？
⇒おっしゃる通りで基本的なことが書いてある参考書的な本と逆引き辞典でやっております。やりたいことができそうなコードを繋げているような状態なのでエラーが頻発してしまいます。教本は随分探したのですが、コレ！という本に出会えておりません。お勧めがあれば教えていただけますでしょうか。

以下も、あまり書かない方法ですが、End プロパティと、Copy メソッドを分かりやすくしたのコードです。
⇒Do while文を使用したものより、さくっと処理できました。
実は質問はやりたいことの第一段階だったりします。。途中でこちらの
コードに変更するかもしれません。ありがとうございます。

回答No.2

o_chi_chi

シート1、シート2はシート名と合っていますか？

あと
Do While Cells(X, "A").Value <> ""
は
Do While Sheets("シート1").Cells(X, "A").Value <> ""

Sheets("シート2").Activate
は必要ないです。

お礼

>o_chi_chiさま

ご回答ありがとうございます。

Do While Sheets("シート1").Cells(X, "A").Value <> ""
Sheets("シート2").Activateは必要ないです。
⇒言われてみるとその通りですね。。

まだ基本文法もわかってないのだなあ、、と反省しきりです。

回答No.1

moon00

多分、最初の
Dim X As Integer
を
Dim X As Long
に変更すれば、動くと思われます。

行数がIntegrでは足りないということだと思うのですが。
（Yも同じように定義していたら、Longにしてください）

お礼

>moon00さま

ご回答ありがとうございます。
早速試してみましたが、longにしても同じ結果でした。

ちなみに、処理する行数は最大で200行となります。
変数の設定をせず、XとYに整数値を代入すると問題なく実行されます。

引き続きご指南いただけますと幸いです。
よろしくお願いいたします。