[[20180816001615]] 『関数もしくはマクロで、ひとつ前の値を記録したい』(たむら) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『関数もしくはマクロで、ひとつ前の値を記録したい。』(たむら)

こんばんは。

ある表で個数を更新して行きたいと思っているのですが、
数量の列の値を変更した場合、その変更前の値を別の一つ隣の列に自動的に
残るようにしたいのですが、新しい値が入力されたら、ひとつ前の値を別の
セルに移すというような事をする事は可能でしょうか?

たとえは、リンゴが10個あった状態から5個に減った場合、
個数列を10→5に書き換えると、自動的に(前の値)列に10が記録されるような
作りにしたいです。

 品名    (前の値)  個数
りんご   10    5

よろしくお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


マクロでChangeイベントを使用すれば可能かと思います。
Activeセルの値を常に記憶して、
該当セルでイベントが発生したら隣のセルに値を出力するみたいな。

(ゆ) 2018/08/16(木) 08:20


ざっくりこんな感じでいけないでしょうか

Public Va As Variant
Public Ro As Variant
Public Co As Variant

Sub Worksheet_SelectionChange(ByVal Target As Range)

Va = ActiveCell.Value
Ro = ActiveCell.Row
Co = ActiveCell.Column

End Sub

Sub Worksheet_Change(ByVal Target As Range)

If Co = 2 And Va <> "" Then

Cells(Ro, Co + 1).Value = Va

End If

End Sub
(ゆ) 2018/08/16(木) 12:50


補足です
挙動が重かったんで調べたら、Changeイベントは、
Application.EnableEventsで適度に無効化しないとでした。

それとブックを開いてセルを移動せずに変更するパターンが考慮されていません。
そこまで考慮する場合、Openイベントを使ってください。
(ゆ) 2018/08/16(木) 14:09


ゆさんが言及されてますが、Application.EnableEventsで、一旦イベント止めないと、無限ループしますね。(テスト時に忘れてえらいことに)

    Private Sub Worksheet_Change(ByVal Target As Range)
        '複数のセルがいっぺんに変更されたら処理しない
        If Target.Count > 1 Then Exit Sub

        Dim buf As Variant, MyRNG As String

        Application.EnableEvents = False

        With Target
            MyRNG = .Address
            buf = .Formula
            Application.Undo
            .Insert Shift:=xlToRight
        End With

        Range(MyRNG).Formula = buf

        Application.EnableEvents = True

    End Sub

(もこな2) 2018/08/16(木) 16:13


失礼しました。質問を勘違いしていました。
入力列がC列だとして、B列に前回値を移動させるのであればこのようなコードでできると思います。
(お試しの際は、シートモジュールに貼り付けのこと)

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim buf As Variant
        Dim MyRNG As Range

        Set MyRNG = Intersect(Columns("C"), Target)
        If MyRNG Is Nothing Then Exit Sub

        Application.EnableEvents = False
        With MyRNG
            buf = .Formula
            Application.Undo
            .Copy .Offset(, -1)
            .Formula = buf
        End With
        Application.EnableEvents = True
    End Sub

なんとなく解説
・Changeイベントは、セルの内容が変更された時に発生しますので、その状態では「前回値」は解りません。
・そこで、現在値をまず覚えておいて、「元に戻す」を実行し、それを隣のB列にコピーします。
・コピーが終わったら覚えておいた内容をそのまま、C列に貼り付けて「変更後」の状態を復元します。
なお、上記の例では、C列以外ではマクロを発動させたくないため、Intersectメソッドを利用して、変更のあったセル(範囲)のうちC列のものだけ処理をするようにしています。(変更のあったセル(範囲)にC列が含まれていなければ、そのまま終了します。)

(もこな2) 2018/08/16(木) 19:02


みなさま、ありがとうございます!!お知恵をいただいた内容で実現ができそうです。
(たむら) 2018/08/18(土) 06:06

なんどもすみません。アドバイス頂いた内容で実現できました!ありがとうございます。

ひとつ、コピー方法を値のみを指定することは出来ないでしょうか?
pastspcialを試してみましたがうまくできませんでした。

よろしくお願いいたします。

(たむら) 2018/08/23(木) 00:54


pastspcialではなくPast「e」Specialですよ。
どのようなコードでうまくいっていませんか?

(ゆ) 2018/08/23(木) 07:04


ありがとうございます。失礼しました。スペル間違いです。
以下のように書いてみたのですが、値のみを貼り付けることができません。
まだ、マクロ勉強中のためちんぷんかんぷんですみません。

With MyRNG

   buf = .Formula
   Application.Undo
   .Copy 
   .PasteSpecial Paste:=xlPasteValues.Offset(, -1)
   .Formula = buf
End With
(たむら) 2018/08/23(木) 18:47

具体的なエラーメッセージも提示いただくと良いと思いますが、おそらく、MyRNG.PasteSpecialとなっているのが原因だとおもいます。
(PasteSpecialメソッドはシートを操作する命令のため、セル(範囲)が格納されているMyRNGを対象にしているとエラーになる)

また、値のみでよいのであれば、そもそもCOPYメソッドでなくて直接Valueプロパティを参照してしまえば良いかも・・・
未テストですが↓のような感じではどうでしょうか?

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim buf As Variant
        Dim MyRNG As Range
        Set MyRNG = Intersect(Columns("C"), Target)
        If MyRNG Is Nothing Then Exit Sub
        Application.EnableEvents = False
        With MyRNG
            buf = .Formula
            Application.Undo
            .Offset(, -1).Value = .Value
            .Formula = buf
        End With
        Application.EnableEvents = True
    End Sub

(もこな2) 2018/08/23(木) 19:58


>.PasteSpecial Paste:=xlPasteValues.Offset(, -1)

構文が間違っています
.Offset(, -1).PasteSpecial Paste:=xlPasteValues

(マナ) 2018/08/23(木) 20:11


実は、この意味がわかっていません。

>buf = .Formula

(マナ) 2018/08/23(木) 20:28


 >(PasteSpecialメソッドはシートを操作する命令のため、セル(範囲)が格納されているMyRNGを対象にしているとエラーになる)

 そんな訳は無いでしょう。

 オブジェクトブラウザーで確認すれば分かりますが、ちゃんとRangeにも紐づいてますよ。

 ここでもRangeのメソッドとしてメンバになってますよ。
https://msdn.microsoft.com/ja-jp/library/microsoft.office.interop.excel.range_members(v=office.11).aspx

(sy) 2018/08/23(木) 20:38


編集かぶったけどそのまま

マナさんの投稿を拝見して、ちょっと実験してみたら、「おそらく〜」は思いっきり私の勘違いですね。失礼しました。

ついでに、Valueプロパティを参照する方法のテストをしてみたところ、Win7&Excel2013の組み合わせではとりあえずうまくいっているように見えます。

buf = .Formula
↑は、実際に定数が入るのか、数式が入るのかわからなかったので、どちらでも対応できるように考えた結果です。
(もこな2) 2018/08/23(木) 20:40


>数式が入るのかわからなかったので、

ありがとうございます。

今回は値のみの転記なので問題ないですが
最初のコードのようにコピーだと、数式によってはだめですね。

(マナ) 2018/08/23(木) 21:18


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.