関数で、返したデータを、コピーして値で再度入

質問

タイトルの件、質問いたします。

下記の画像をご覧ください。

まず、台帳ファイルについて、説明します

B２に、０1　と入力すると、C2とD2に、データファイルから、情報が呼び出されます。
C2とD2に入っている関数は

C2の関数⇒　=VLOOKUP($B2,[データ.xls]データ!$B$2:$D$4,2,FALSE)
D2の関数⇒　=VLOOKUP($B2,[データ.xls]データ!$B$2:$D$4,3,FALSE)

です。

次からが質問です

このC2とD2に関数によって、呼出されているデータを、コピーして
右クリックし、形式を選択して、貼付けで、【値】として貼り付けます。

この作業をマクロもしくは、関数で自動化できませんでしょうか？？

実際の運用は
台帳ファイルに日付と番号を入力し、その後
関数で、呼出された、C2、D2のデータを値として、自動で、貼り付けたいのです。
今は、前述の説明のとおり、手動で、右クリックして、形式を選択をして貼付けで
やっています。

なぜ、値で貼り付けたいかといいますと、データファイルを定期的に差替え
差替え後に、０１番の人の情報がない場合があるかです。

その場合は、０１番の人の情報がなしで、表示されてしまうからです。

以上、ご存知の方、いらっしゃいましたら、宜しくお願いします。

回答No.2

tom04 · ベストアンサー

こんばんは！

一例です。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub Sample1()
Dim i As Long
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
With Range(Cells(i, 3), Cells(i, 4))
.Value = .Value
End With
Next i
End Sub

※　B列にデータを入力した時点でマクロを実行する方法もあります。
この場合はすでに関数で表示されているC・D列はそのまま変更がありません。

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Columns(2)) Is Nothing Or Target.Count <> 1 Then Exit Sub
If WorksheetFunction.CountA(Target.Offset(, 1).Resize(1, 2)) = 2 Then
With Target.Offset(, 1).Resize(1, 2)
.Value = .Value
End With
End If
End Sub

こんな感じではどうでしょうか？m(_ _)m

補足

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

試してみたところ、うまくできました。

こんな、マクロが組めるのはすごいですねぇ。驚きでした。

関数で、データを呼び出した部分だけ、関数⇒値に変わる
もとの、データを呼び足していない部分は、値にならない⇒関数が消えない

理想的な、マクロでした。
作っていただいた、マクロは、理解できるようにがんまります。

ありがとうございまいした。

回答No.4

FEX2053

>関数でデータを返している部分のみを、自動で選択して、
>自動で貼付ることは出来ますでしょうか？

これ、非常に難しいです。というのも、データが入っていると
言う事実をExcelが認識できないからです。セル内容を
検索すると、未入力のセルは「値ゼロ」または「値空白」
として評価されますが、このとき、セロを入力したセルとの
区別が付かないからです。

データが「必ず正の値」だとかなら、

Sub Macro1()
IF Selection.Offset(n,m).Value > 0 then
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End if
End Sub

とかで逃げられますが、これとて、上記のn,mは、データを
入力するセルの場所を示す具体的な値に書き変えないと
動作しませんので、これがチェックする関数によって違うと
すると、同じ関数では簡単には処理できなくなります。

（まあ、リストを作って参照させるとか、力技で何とかする
方法は無いわけじゃないですけど、大変です）

むしろ、処理が完全に終わってからシートごと値コピーする
とか、結果を置いておくシートを別建てにするとか、作業の
手順を見直した方が良いかもしれません。

回答No.3

tsubuyuki

正直、コピー→値を貼り付け 程度であれば、
手でやってもそんなに時間はかからないでしょう。

> B２に、０1　と入力する
> 台帳ファイルに日付と番号を入力し、
のですから、最終行はすぐに取れる（例えば「Ｃｔｒｌ＋↓」など）でしょうから、
例えば（２００７or２０１０をご使用のようですから）、
　・Ａ１を左クリック
　・Ｃｔｒｌ＋Ｓｈｉｆｔ＋↓（キーボードの下向き矢印）
　・マウス右クリック→コピー（Ｃｔｒｌ＋Ｃ　でコピーでも可）
　・マウス右クリック→値を貼り付け
これだけですが、マクロでやらなきゃならないくらい手間ですか？

回答No.1

FEX2053

マクロなら可能です。

例えばこういうコードをVBEで書き、[Alt]+{F8]を押して、
このマクロをどれかのキーにアサインすればオッケー。
例えば[Ctrl]+[Z]で「その場に値コピー」されます。

Sub Macro1()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

ただ、これやっちゃうと、元の式が消えてしまいます。
消えちゃう式をどうやって復活するか・・・は別の話に
なりますよ。

どっちかと言うと、

Sub Macro1()
Selection.Copy
Selection.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

などとやって、一つ下に張り付ける・・・などの方が
良いんじゃないかと思います。

お礼

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

コピーしたものが、値だけ、貼付ことができました。

さらに、質問をさせて頂きたいのですが

関数でデータを返している部分のみを、自動で選択して、自動で貼付ることは出来ますでしょうか？
関数で返している部分はもちろん、関数が消えてしまってOKです。

ただ、まだ、データが入っていない部分⇒番号が入力されていない部分は
関数が消えたら困ります。

以上、宜しくお願いします。