Excel vba 参照セルが変更された時

Question

こんばんは。

”シート1”の”A1"セルに、”シート２”の”B2"セルを参照しているとします。

”シート２”の”B2"セルの内容を変更したとき、”シート1”の”G1"セル内容を変更したい場合どうすればいいでしょうか。

ご回答よろしくお願いいたします。

ggrksdqn · Accepted Answer

No.3 です。

No.6 で補足した下のコードは入ってますか。

>Private Sub Workbook_Open()
>initOrder
>End Sub

もしそれでも、エラーになるの場合は、ブックを開き直すか、
VBEからinitOrderを実行してください。

監視対象のシートを変数wsOrderに入れる処理はブックを開いた時に一度だけ実行されます。
ブックを開いた後にVBEでリセットボタンを押すなどしてマクロが止まってしまうと、変数wsOrderは空っぽのままになるので、エラーになります。
ブックを開いてからVBEでコードをいじらないかぎり、この仕様でいいと思いますが、気になるなら、Sheet2のWorksheet_Activate()イベントにでも
initOrder　を入れておけば良いかと思います。

＞再計算を監視する対象になるシートが50個ある

よく分かりません。50枚のシートは全てSheet1と同じようにSheet2の注文列を参照しているのでしょうか。つまりSheet2の注文が変更されたら、50枚のシートそれぞれにつき、該当する注文のステータスが空白になるということでしょうか。

それなら、シート50枚のうちマクロでステータスの更新をするのは、キーになる1枚だけにしておいて、他の49枚のステータス列には、キーになるシートのステータス列を参照する参照式を入れておけば良さそうです。

試しにシートすべてのステータス更新処理をマクロで実行してみると
時間がかかりすぎて使いものになりませんでした。50シートではなく、10シートで試しました。それでも再計算前の10,000行と再計算後の10,000行を比較する処理を10シートを分実行するので時間がかかるのは仕方ないです。

銀鱗 · Answer

質問者さんがやりたいことは、
　「参照元になるセルの値が変わったら、特定のセルの表示を【空白】にしたい」
って事で良いのでしょうか？

これならVBAを使う必要があります。

・・・
でもそれで良いの？
少しずつ条件後出しされるので、ちょっと不安です。

A1セル：アディダス
　C1セル：出荷済み（これは手作業）
　↓
”ナイキ” の表示に切り替える。
　A1セル：ナイキ
とする。
　C1セル：空白（自動更新で空白にする）
になる。
　↓
もう一度 ”アディダス” の状態を【確認したい】ので、
　A1セル：アディダス
とした場合、
　C1セル：空白
になります。

…とまあ、意図しない動きをする可能性もあるのです。
この場合は、C1セルの状態をSheet2に反映させる必要がありますね。

・・・

マクロ全体を作って欲しいのか、特定のセルを【空白】にする方法を知りたいのかも考えてください。
マクロ全体を作って欲しいのであれば、ここは場違いです。
もしも代わりに全部作って欲しいとお考えであれば、有料サイトで依頼することを勧めます。
ここは、自分で作れるようになるためのアドバイスをするところですので...。
（ごめんね。「コード１つ示さず何言ってんだコノヤロー」って思っているでしょうけど）

「Sheet2のA1セルの値が変更されたかを検出する。
　変更されたらSheet1のC1セル（G1セル？）の値を【空白】にする」
のか
「Sheet2のA1セルの値が変更されたかを検出する。
　変更されたらSheet1のC1セル（G1セル？）の値をSheet2のC1セル（仮）の値にする。
　Sheet1のC1セル（G1セル？）の値が変更されたかを検出する。
　変更されたらSheet2のC1セル（仮）にコピーする」

など、アルゴリズムを明確にしないとプログラムってまともに動くものができないんです。
ExcelのマクロはVBA（Visual Basic for Application）というプログラム言語なんです。

・・・余談・・・

さすがにコードを示した回答があるのに自分が示さないのは、色々と問題があるので、
基本コマンドを示してみます。

C3セルを空白にするには
　Cells(1, 3).Clear
と、Cellsコマンドでセル番地を指定してClearを指定します。
（これが一番分かりやすいはず）

セルの比較には（A1セルとB1セルを比較する例）
　If StrComp(Cells(1,1),Cells(1,2)) = 0 Then
　　MsgBox "等しい"
　　Else
　　MsgBox "等しくない"
　End If
のように、StrCompコマンドで比較します。
（等しいと「0」、等しくないと「-1」が返ります）

ggrksdqn · Answer

No.3 です。
コードを入れ忘れました。
次のコードをThisWorkbookモジュールに追加してください

Private Sub Workbook_Open()

initOrder
    
End Sub

ggrksdqn · Answer

No.3 です。

自信はありませんが、要件が理解できたので
コードを書いてみました。

＜前置き＞
説明の便宜上、こうします。

Sheet1のA列　および、その参照先になっているSheet2の列（ここではA列）を　”注文”　と呼びます

そして、Sheet1のG列 を　"ステータス"と呼びます。

＜おおまかな処理の流れ＞

1. Sheet2の注文が変更される
2. Sheet1の注文が変更される
3. Sheet1の再計算イベント(Calculate())が発生する
4. Sheet1の注文10,000件分それぞれについて、再計算イベント発生前と再計算イベント発生後で変更があったか調べる
5. 変更があったら、Sheet1のステータスを空白にする

＜コードに関する補足＞
- このコードはダミーデータ10,000件を使ってテストしました。
- 一般的な環境（ノートＰＣ, Windows10, Office2010)でもマクロが動いてると感じさせない処理速度で動作しています。
- Calculate()は再計算”後"に発生するイベントです。再計算"前"のイベント
　は存在しないので、Workbook_Open()イベント発生時に再計算前の状態を保存します
- 注文が変更されたがどうかは、Sheet2のWorkSheet_Change()イベントでIf Target.Column = 1 で調べるのが理想的だと思いますが、できない事情がおありなようなのでこの手は使いませんでした。
- イベントを使わず、Sheet1の状態をポーリングするやり方も試しましたが、動きがいまいちだったのでやめました。
　
＜コード＞
'*******************************
'Sheet1のシートモジュール
'*******************************
Private Sub Worksheet_Calculate()
    updateOrderStatus
End Sub

'*******************************************
'標準モジュール
'*******************************************
'再計算を監視する対象になるシートの名前
Public Const WS_NAME As String = "Sheet1"

'監視する対象になる範囲
Public Const SRC_RANGE As String = "A1:A10000"

'注文ステータスが入っている列
Public Const COL_ORDER_STATUS As Long = 7

'再計算を監視する対象になるシート
Public wsOrder As Worksheet

'再計算後の状態
Public curOrder

'再計算前の状態
Public prevOrder

'初期化（起動時）
Public Sub initOrder()
    '再計算を監視する対象になるシート
    Set wsOrder = Sheets(WS_NAME)
    
    '現在の状態をVariant型に入れる
    prevOrder = wsOrder.Range(SRC_RANGE)
End Sub

'注文ステータスを更新する
Public Sub updateOrderStatus()
    
    '再計算後の状態をVariant型に入れる
    curOrder = wsOrder.Range(SRC_RANGE)
    
    If IsEmpty(prevOrder) = False Then
    
        '各セルの内容の再計算後の状態を見る
        Dim i As Long
        For i = 1 To UBound(curOrder, 1)
    
            '再計算後の状態と再計算前の状態と比べる
            '変化があれば、ステータスを更新（空白に）する
            If curOrder(i, 1) <> prevOrder(i, 1) Then
                wsOrder.Cells(i, COL_ORDER_STATUS).Value = ""
                
                '1度に1件しか入力/変更しないなら
                'ここでExit For して高速化する
    '            Exit For
                
            End If
        Next
    
    End If
    
    prevOrder = curOrder

End Sub

銀鱗 · Answer

G1セルに
　＝IF(A1=200,"凶",IF(A1=6000,"大吉"),"")
とかで良いと思いますよ。

たくさんあるなら、数字に対して表示する一覧表を作っておき、
それをVLOOKUP関数で参照させればいい。
そのほうがメンテナンスも視認性も良い。

マクロにする必要は感じられませんが、
関数と同じような動作をさせれば良いと思います。
どのような手順で表示させているのかを、一つ一つ把握するようにしてみましょう。

ggrksdqn · Answer

こんなんならできますけど、ご希望に添えるかどうか。

＜やり方＞
1. どっかに対照表を作っておく

場所: Sheet2!A4:B7

内容:
200  | 凶
1000 | 小吉
3000 | 中吉
6000 | 大吉

2. ちょっとした式をいれる

場所: Sheet1!G1

①VLOOKUP関数を使う

=VLOOKUP(A1,Sheet2!A4:B7,2,FALSE)

②INDEX関数とMATCH関数を組み合わせる

=INDEX(Sheet2!A4:B7,MATCH(A1,Sheet2!A4:A7,0),2)

＜備考＞

入力と結果の組み合わせが２つとか３つくらいなら
IF関数で事足ります。

（例）200なら凶、 6000なら大吉

=IF(A1=200,"凶", IF(A1=6000,"大吉",""))

銀鱗 · Answer

ごめん。
やっぱり何を言っているのかよく分からない。

Sheet1のA1セルに
　＝Sheet2!B2
と入力しているならいざ知らず、
　"Sheet2!B2"
では
A1セルに
　「Sheet2!B2」
と文字列が表示されるだけです。
A1セルの内容は数式じゃない。（←ここ重要）

・・・

常にA1セルはSheet2のB2セルの内容を反映させ、
G1セルにもSheet2のB2セルの内容を反映させたい。
…というの？？

曖昧と言うか、こっちらが推測して提案しないと意味が通らないのでは質問にすらなりません。

Sheet1のA1セル、
Sheet1のG1セル、
Sheet2のB2セル、
それぞれどんな関係にしたいのですか？
具体的な ”値” を例に挙げてみてください。

・・・余談・・・

前の回答で答えた「文字列」と「数式」を実際にExcelに入力して、
その結果を質問者さんが考えている物と比較してみてください。

銀鱗 · Answer

質問の意図がよく分からないのですが、
ひょっとして……

Sheet1のA1セルに
　"Sheet2!B2”
と入力して、
Sheet1のG1セルに
　=INDIRECT(A1)
と入力。

Sheet2のB2セルの値がSheet1のG1セルに反映される。

……ということをマクロでやりたいという事でしょうか？

Excel vba 参照セルが変更された時

No.3 です。

質問者さんがやりたいことは、

No.3 です。

No.3 です。

G1セルに

こんなんならできますけど、ご希望に添えるかどうか。

ごめん。

質問の意図がよく分からないのですが、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング