セル入力のタイミングで数式をコピーする方法

質問

A列に数値を入力→Q2～V2に入っている数式を入力列のQ～V列に
コピーをできるようにしたいのですが、、

下記のコードを実行したところ、反応せず（エラーも出ず）で
困っております。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long

With Target

myRow = .Row + 1

If .Column = 1 And TypeName(.Value) = "long" Then

　　 Range("Q2:V2").Copy
Cells("Q" & myRow & ":V" & myRow).PasteSpecialPaste:=xlPasteFormulas
Application.CutCopyMode = False

End If

End With

End Sub

エラーがないため、間違った部分も判らず立ち往生しております。

アドバイス・指摘をお願いいたします！

回答No.8

watabe007

>特に、IsNumeric関数は全く知らなかったので。。
IsNumeric関数は数値に対してTrueを返しますがDeleteキーで消去した時もTrueを返しますので
消去に対して実行したくない場合は対策が必要ですよ
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If IsNumeric(.Value) Then
MsgBox .Value & " は、数値です"
Else
MsgBox .Value & " は、数値以外です"
End If
End With
End Sub

回答No.7

myRange

何回目の登場でしょうか、myRangeです。

＞Q列とV列に関しては、ばっちりコピーできているのですが
＞範囲内のその他の列（S列など）にはコピーできていません。
＞コピー元にはちゃんと数式が入っているのですが。。

俄かには信じられない現象です。
ちゃーーーーーーーーーんと確認しての発言でしょうねぃ？

「勘違いでした～」は無しですよ。(^^;;;

再度確認して、それでもその現象を確認できるなら
実際のコードを全てアップ願います。
以上です。

お礼

ご返信が遅くなりましてすみません。

myRangeさんが提示してくださったコードの中で
「～PasteSpecial xlPasteFormulas」
を
「～PasteSpecial　Paste:=xlPasteFormulas」
で実際は組んでいたのですが、再度やりなおしたところ
どちらでも作動しました。

ご心配をお掛けいたしました<m(__)m>

回答No.6

hige_082

回答は出ているようですが、参考まで

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And IsNumeric(Target.Value) Then Range("Q2:V2").Copy Cells(Target.Row, 17)
End Sub

お礼

ご教示いただきましてありがとうございます。

貼り付けて使用してみたところ、ばっちり動きました!
同じ動作でもコードがこんなにすっきりするとは驚きです。。
特に、IsNumeric関数は全く知らなかったので。。

hide_082さんくらい書けるよう、がんばります！

回答No.5

myRange

またまた登場、myrangeです。

回答４、myRangeです。

質問者はちょとケアレスミスが多い傾向がありますねぃ。(^^;;;
コードを書くときはも少し注意深くなくてはいけません。
と、偉そうなことを言ってみる。。。(^^;;;

-----------------------------------------------------------
＞TypeName(.Value) = "Double"
＞テキスト本で復習してみましたが、なぜlongは間違いなのでしょうか？

TypeName関数のヘルプを見ましたか？
ヘルプには次のように書いてあります。

【変数】に関する情報を提供する文字列型 (String) の文字列を返します

Target.Valueは変数ですか？　違いますよね。
要するに、Target.Valueが数値だったら、Doubleが返るという仕様になってるということです。
ヘルプを見ながら変数に値をいれどんなものが返ってくるか試してみるといいでしょう。
--------------------------------------------------------------

＞参照が間違っている（.Rowの部分）とのことでエラーがでます
＞myRow = .Row

こら、こら、With Targetの外側にあればエラーがでるのは当然のことででは？

-------------------------------------------------------

それから、提示のコードは実際のコードそのままコピペしてここに貼り付けましたか？

Range("Q" & CStr(myRow) & ":V" &CStr( myRow)).PasteSpecialPaste:= xlPasteFormulas

これあきらかにミスでしょう。
このコードは編集の段階でエラーが出たはずです。
最初の質問でもそうなってますが、転記ミスだろうとそのままにしておきましたが。。。
それを無視して実行しても今度はコンパイルエラーになるはずです。

＞PasteSpecialPaste

PasteSpecial●Paste　　←　●はスペース

よって提示のコードを修正すると以下のようになります。

'---------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
　With Target
　If .Column = 1 And TypeName(.Value) = "Double" Then
　　　Range("Q2:V2").Copy
　　　Range("Q" & .Row & ":V" & .Row).PasteSpecial xlPasteFormulas
　　　Application.CutCopyMode = False
　End If
　End With
End Sub
'------------------------------------------------------

以上です。

補足

色々とご指摘いただきましてありがとうございます。
転記ミスなども多く。。すみません。

>【変数】に関する情報を提供する文字列型 (String) の文字列を返します
なるほど！勉強になりました。

myRangeさんに修正いただいたコードで、エラー克服できました。
ありがとうございます。

ただ、、、
>Range("Q" & .Row & ":V" & .Row).PasteSpecial xlPasteFormulas
Q列とV列に関しては、ばっちりコピーできているのですが
範囲内のその他の列（S列など）にはコピーできていません。
コピー元にはちゃんと数式が入っているのですが。。

再びご指南のほどお願いいたします。

回答No.4

myRange

＞下記のコードを実行したところ、反応せず

TypeName(.Value) = "long"

回答１でも指摘されてますが、これが常にFlaseだからです。
正しくは、

TypeName(.Value) = "Double"

ですが、そこを直しても提示のコードには文法ミスがあるのでエラーが出ます。
現在エラーが出ないのは上記の条件文のせいでそこまで行ってないからです。

＞Cells("Q" & myRow & ":V" & myRow).PasteSpecial

Cellsプロパティではこのようにセル範囲は指定できません。
セル範囲を指定するときは、Rangeプロパティを使います。

Range("Q" & myRow & ":V" & myRow).PasteSpecial

で、提示のコードを尊重すると以下のようになるわけです。

'----------------------------------------------　
Private Sub Worksheet_Change(ByVal Target As Range)
　Dim myRow As Long

　With Target
　If .Column = 1 And TypeName(.Value) = "Double" Then
　　　myRow = .Row + 1
　　　Range("Q2:V2").Copy
　　　Range("Q" & myRow & ":V" & myRow).PasteSpecial xlPasteFormulas
　　　Application.CutCopyMode = False
　End If
　End With
End Sub
'---------------------------------------------------

それから他の回答でも指摘されてますが、
＞myRow = .Row + 1
これは質問の文言と矛盾してます。
以上です。

補足

ご回答いただきましてありがとうございます。

Cellsプロパティの範囲設定、思いっきり間違えてましたね。
>TypeName(.Value) = "Double"
テキスト本で復習してみましたが、なぜlongは間違いなのでしょうか？
（当たり前すぎる質問でしたらすみません）

その他、ご指摘いただいた内容をふまえて

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim myRow As Long
　myRow = .Row

　With Target
　If .Column = 1 And TypeName(.Value) = "Double" Then

　　　Range("Q2:V2").Copy
　　　Range("Q" & CStr(myRow) & ":V" &CStr( myRow)).PasteSpecialPaste:= xlPasteFormulas
　　　Application.CutCopyMode = False
　End If
　End With
End Sub

としてみました。

A列に入力すると、参照が間違っている（.Rowの部分）とのことでエラーがでます。しかし、エラー無視で他の列（ここに入力した数字はコピーする数式に参照されています）にも入力を重ねると数式はちゃんとコピー
され、問題なく動作してしまいます。

Changeイベントがらみでしょうか。。

引き続き、ご指南いただけますと幸いです。

どうぞ宜しくお願いいたします。

回答No.3

watabe007

参考に
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
'値が入ったセルがA列でなく、または数値でない場合はExit
If .Column <> 1 Or VarType(.Value) <> vbDouble Then Exit Sub
Range("Q2:V2").Copy
Cells(.Row + 1, "Q").Resize(, 6).PasteSpecial Paste:=xlPasteFormulas
End With
End Sub

お礼

お返事が遅くなりましてすみません。

ご教示いただきましてありがとうございました。
「Cells(.Row + 1, "Q").Resize(, 6).PasteSpecial Paste:=xlPasteFormulas」の.row＋1を.rowに修正して
実行しましたら（＋1は私の間違いです）、見事に思い通りに動きました。

別方法を教えていただきまして感謝しております。
今後も地道に色々と覚えていきたいです！

回答No.2

Sinogi

>下記のコードを実行したところ、反応せず（エラーも出ず）で困っております。

このコードはどこに記述していますか?
目的のシートに記述できていますか?

>If .Column = 1 And TypeName(.Value) = "long" Then
And TypeName(.Value) = "long"　は不要でしょう

>Cells("Q" & myRow & ":V" & myRow).PasteSpecialPaste:=xlPasteFormulas

myRowが数値（Long)なのでエラーになります。
CSTR(myRow)で文字にしましょう。

前後しますが
>myRow = .Row + 1

なぜ +1 なのでしょうか?
それが希望の動きならOKですが、質問文を見る限り
myRow = .Row
だと思います。

お礼

ご回答いただきましてありがとうございます。
コードは目的のシートに記述しております。

>And TypeName(.Value) = "long"　は不要でしょう
省いてみましたが、結果は同じくでした。

> CSTR(myRow)で文字にしましょう。
修正したら、この部分に関してはうまくいったようです！

>myRow = .Rowだと思います。
間違っていたようですが、修正後も参照違いで再びエラーとなりました。。

回答No.1

fujillin

ぱっと見ですが、
TypeName(.Value) = "long"
が成立していないから、そのまま「正しく」終了しているだけは？

お礼

ご回答いただきましてありがとうございます。
数値を入力することを認識させるために付加しましたが
違っていたようですね。