EXCELのVBAでセル値の移動でエラー

質問

EXCEL2002のVBAでセル値の移動をVBAでやりたいのですが
(1)の様に1行は出来るのですが、(2)の様に２行を移動させるとエラー
（実行時エラー1004　アプリケーション定義またはオブジェクト定義のアラーです。）が出てしまいます。
(2)のマクロでどうしてエラーが出るのか分かりません。
エラーを出さずに2行目を移動させる方法を教えてください。

(1)_____________________________________＿＿＿＿＿＿＿＿＿＿＿＿
If Range("L1") <> detachn Then
i = 9
While i >= 1
Cells(1, i + 13).Value = Cells(1, i + 12).Value
i = i - 1
Wend
Cells(1, 13).Value = Range("L1").Value
datachn = Range("L1").Value
End If
(2)________________________________＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

If Range("L1") <> detachn Then
i = 9
While i >= 1
Cells(1, i + 13).Value = Cells(1, i + 12).Value
Cells(2, i + 13).Value = Cells(2, i + 12).Value ←ここでエラー
i = i - 1
Wend
Cells(1, 13).Value = Range("L1").Value
Cells(2, 13).Value = Range("L2").Value
datachn = Range("L1").Value
End If

回答No.2

GreatDragon · ベストアンサー

再び、＃１の GreatDragon です。

ワークシートモジュールに記述されていたのですね。
それでは動作がおかしくなったり、エラーが発生するのは当然です。

ユーザがシートを変更
　↓
イベントプロシージャの自動実行
　↓
プロシージャによる１箇所のセルの変更
　↓
プロシージャが実行途中でもイベント発生のため、
更にイベントプロシージャを自動実行
　↓
プロシージャによる１箇所のセルの変更
　↓
プロシージャが実行途中でもイベント発生のため、
更にイベントプロシージャを自動実行

という具合に、おかしな動きをしてしまいます。

なので今回の場合、イベントを一時的に無効にしてみてください。

（例）

Option Explicit

Dim i As Integer
Dim datachn As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("L1") <> datachn Then
Application.EnableEvents = False 'イベント無効
i = 9
While i >= 1
Cells(1, i + 13).Value = Cells(1, i + 12).Value
Cells(2, i + 13).Value = Cells(2, i + 12).Value
i = i - 1
Wend
Cells(1, 13).Value = Range("L1").Value
Cells(2, 13).Value = Range("L1").Value
datachn = Range("L1").Value
Application.EnableEvents = True 'イベント有効
End If
End Sub

なお、標準モジュールでは正しく動作します。

お礼

GreatDragonさん、回答ありがとうございます。
イベントを無効したものをワークシートモジュールに記述して確認してみたところ見事に動きました。

「標準モジュールでは正しく動きます」とあるのですが、標準モジュールにPrivate Sub Worksheet_Change(ByVal Target As Range)を記述してもセルの変更イベントを無視すると思うのですが、Private Sub Worksheet_Change(ByVal Target As Range)以外にセルの変更を感知する方法ってあるのでしょうか？

回答No.4

GreatDragon

＃１～＃３の GreatDragon です。

> Private Sub Worksheet_Change(ByVal Target As Range)以外に
> セルの変更を感知する方法ってあるのでしょうか？

ワークシートの変更を検出してプロシージャを実行できるのは
　Worksheet_Change
　Workbook_SheetChange
の２つだけと思っています。

ご存知とは思いますが、
　Worksheet_Change：記述したシートの変更を検出
　Workbook_SheetChange：記述したブックのすべてのシートの変更を検出
ですね。

お礼

GreatDragonさん、毎度、回答ありがとうございます。
おかげ様で自分のやりたかったことができました。

回答No.3

GreatDragon

言葉が足りませんでした。

＞なお、標準モジュールでは正しく動作します。

イベントを無効にしなくても、標準モジュールに記述したら
正しく動作すると言う意味です。

回答No.1

GreatDragon

こんにちは。

こちらでは実行時エラーは確認できませんでしたが、
エラーとなる原因が分からない時は、前後の改行も含め
その行を削除後再入力すると解消できる場合があります。

それと気になる点がいくつかありますが
ご承知であれば読み飛ばしてください。

１．変数名が違う
　　detachn と datachn のスペルが異なるのですが、
　　こういう誤りを防ぐためには、Option Explicit を
　　記述し、Dim ステートメントで変数を定義する。

２．セル範囲を指定してからまとめて転記したほうがスムーズ
　　（例）
　　Dim DownRow As Long
　　DownRow = Range("L1").End(xlDown).Row
　　Range(Cells(1, 13), Cells(DownRow, 22)).Value _
　　　　= Range(Cells(1, 12), Cells(DownRow, 21)).Value

補足

GreatDragonさん、回答ありがとうございます。
新しくBookを作ってみました。
私の環境では下記マクロでエラーが発生してしまいますがGreatDragonさんの環境ではエラーが発生しないとはExcelに問題があるのでしょうか？

Dim i As Integer
Dim datachn As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("L1") <> datachn Then
i = 9
While i >= 1
Cells(1, i + 13).Value = Cells(1, i + 12).Value
Cells(2, i + 13).Value = Cells(2, i + 12).Value'←エラー
i = i - 1
Wend
Cells(1, 13).Value = Range("L1").Value
Cells(2, 13).Value = Range("L1").Value
datachn = Range("L1").Value
End If
End Sub