スポンサードリンク

仕事でよく使うVBAなんですが、どうしてもRangeプロパティを使わないといけない時に変数を使おうとするとRangeプロパティにセル番地を指定するCellsプロパティや
行の指定をするRowsプロパティ、列を指定するColumnsプロパティを組み合わせて使うことがあります。
マクロを実行したら「実行時エラー 1004」が出て困ることがよくあるんです。
これを見てくれている方も同じ悩みだと思います。
今回、RangeプロパティでCellsやRows、Columnsを組み合わせて使うと「実行時エラー 1004」が出る時の対処法を覚書で残しておきます。
スポンサードリンク
RangeプロパティでCellsやRows、Columnsを組み合わせて使うと「実行時エラー 1004」が出る

このようなデータが入っているシートに対してVBAで範囲指定して削除するとします。
下記のように、例えばSheet1のある範囲のデータの値を削除するVBAを標準モジュール記述したものを【Sheet2】をアクティブにして実行します。
Public Sub test() ThisWorkbook.Worksheets("Sheet1").Range("A1:C10").ClearContents 'A1~C10の範囲 ThisWorkbook.Worksheets("Sheet1").Range("D:F").ClearContents 'D~F列すべて ThisWorkbook.Worksheets("Sheet1").Range("11:15").ClearContents '11行目から15行目 End Sub

こんな感じに指定範囲が削除されます。
このVBAを変数を使うためにCellsやRows、Columnsを組み合わせてみます。
変数を使って同じ結果になるように標準モジュールに記述したものを【Sheet2】をアクティブにして実行します。
Public Sub test2() Dim i As Long Dim j As Long Dim k As Long i = 10 j = 6 k = 15 ThisWorkbook.Worksheets("Sheet1").Range(Cells(1, 1), Cells(i, 3)).ClearContents 'A1~C10の範囲:Cells使用 ThisWorkbook.Worksheets("Sheet1").Range(Columns(4), Columns(j)).ClearContents 'D~F列すべて ThisWorkbook.Worksheets("Sheet1").Range(Rows(11), Rows(k)).ClearContents '11行目から15行目 End Sub

すると結果は「実行時エラー 1004」が出てしまいます。
Rangeプロパティ内をCellやColumns、Rowsで変更して修正すると起きてしまう現象です。
僕もよくやってしまいます。
「実行時エラー 1004」が出て、いつも気付くのに遅くなってしまいます。
では、この「実行時エラー 1004」の解決法です。
「実行時エラー 1004」の解決法
先ほどの「実行時エラー 1004」を解決するために、下記のように修正します。
Public Sub test3() Dim i As Long Dim j As Long Dim k As Long i = 10 j = 6 k = 15 ThisWorkbook.Worksheets("Sheet1").Range(ThisWorkbook.Worksheets("Sheet1").Cells(1, 1), ThisWorkbook.Worksheets("Sheet1").Cells(i, 3)).ClearContents 'A1~C10の範囲:Cells使用 ThisWorkbook.Worksheets("Sheet1").Range(ThisWorkbook.Worksheets("Sheet1").Columns(4), ThisWorkbook.Worksheets("Sheet1").Columns(j)).ClearContents 'D~F列すべて ThisWorkbook.Worksheets("Sheet1").Range(ThisWorkbook.Worksheets("Sheet1").Rows(11), ThisWorkbook.Worksheets("Sheet1").Rows(k)).ClearContents '11行目から15行目 End Sub
何を修正したかというと、Rangeプロパティ内のCellやColumns、Rowsにどのシートなのか場所を教えてあげないといけないので、【ThisWorkbook.Worksheets(“Sheet1”).】をRangeプロパティ内のCellやColumns、Rowsに指定しただけです。
どのシートなのか場所を教えてあげないと、Rangeプロパティはシート場所を指定しているのに、Rangeプロパティ内のCellやColumns、Rowsは場所がないのでアクティブシートのセルを選択することになってしまいます。
つまり、『Sheet2』をアクティブにした状態でもRangeプロパティは『Sheet1』が指定されているので、『Sheet1』を見に行くのですが、Rangeプロパティ内のCellやColumns、Rowsはアクティブシートである『Sheet2』を見に行くことになるため、そこで「実行時エラー 1004」が起きてしまいます。
ちなにみ、『Sheet1』をアクティブにした状態で「実行時エラー 1004」が起きたVBA(test2)を実行しても「実行時エラー 1004」は起きません。
また、「実行時エラー 1004」が起きたVBA(test2)を『Sheet1』のシートモジュールに書いた場合もエラーは起きません。
スポンサードリンク
「実行時エラー 1004」の解決法の補足
Withステートメントを使ってtest3のVBAを書き直す事もできますよ。
Public Sub test4() Dim i As Long Dim j As Long Dim k As Long i = 10 j = 6 k = 15 With ThisWorkbook.Worksheets("Sheet1") .Range(.Cells(1, 1), .Cells(i, 3)).ClearContents 'A1~C10の範囲:Cells使用 .Range(.Columns(4), .Columns(j)).ClearContents 'D~F列すべて .Range(.Rows(11), .Rows(k)).ClearContents '11行目から15行目 End With End Sub
Rangeプロパティ内にあるCellやColumns、Rowsの前に「.(ドット)」がポイントです。
シート指定を1つで行るので管理はし易いかと思います。
RangeプロパティでCellsやRows、Columnsを組み合わせて使うと「実行時エラー 1004」が出るまとめ
RangeプロパティでCellsやRows、Columnsを組み合わせるととても便利なのですが、シート指定をし忘れてしまうことがありますのでご注意して下さい。
僕もよくやらかして、悩んで気付くのに時間がかかりますw
スポンサードリンク