VBAでセルのコピーをすると、エラーになる

質問

=IF(COUNTIF('5月'!B4:I13,E13)=0,"",COUNTIF('5月'!I:I,E13))というセルを
コピーして、別のシートのセルに貼り付けたのですが、値が「0」の場合「””」が
セルに張り付いてしまい、その後の計算ができません。
「””」を本当の空欄にするにはどうしたらいいのでしょうか？

Sub 転記()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim SN As String
SN = Month(Now())
Set sh1 = Sheets(SN)
Set sh2 = Sheets("差出票")

sh1.Range("A35").End(xlUp).Offset(1) = sh2.Range("B9")
sh1.Range("A35").End(xlUp).Offset(0, 1) = sh2.Range("F13")
sh1.Range("A35").End(xlUp).Offset(0, 2) = sh2.Range("F14")
sh1.Range("A35").End(xlUp).Offset(0, 3) = sh2.Range("F15")
sh1.Range("A35").End(xlUp).Offset(0, 4) = sh2.Range("F16")
sh1.Range("A35").End(xlUp).Offset(0, 5) = sh2.Range("F17")
sh1.Range("A35").End(xlUp).Offset(0, 6) = sh2.Range("F18")
sh1.Range("A35").End(xlUp).Offset(0, 7) = sh2.Range("F19")

End Sub

回答No.10

kagakusuki · ベストアンサー

>シート「差出票」＝当日郵送するレターの数を表示し、郵便局へ出します。F13セルに
>=IF(COUNTIF('5月'!I:I,E13)=0,"",COUNTIF('5月'!I:I,E13))

>シート「5」のB4からJ34までは郵便の数が入るので、「””」が入っているとその後の計算に支障があります。
>=SUM(B12*$B$3,C12*$C$3,D12*$D$3,E12*$E$3,F12*$F$3,G12*$G$3,H12*$H$3)
という式をL12に入れています。

という事だけが問題なのであれば、差出票!F13セルの関数を

=IF(COUNTIF('5月'!I:I,E13)=0,0,COUNTIF('5月'!I:I,E13))

とすれば良い様な気も致しますが、それでは駄目なのでしょうか?
　或いは、L12セルの関数を

=SUM(SUM(B12)*$B$3,SUM(C12)*$C$3,SUM(D12)*$D$3,SUM(E12)*$E$3,SUM(F12)*$F$3,SUM(G12)*$G$3,SUM(H12)*$H$3)

とすれば、""が入っていてもエラーとはならずに計算が行われます。
　後、

=SUM(B12*$B$3,C12*$C$3,D12*$D$3,E12*$E$3,F12*$F$3,G12*$G$3,H12*$H$3)

の様に、一々、セルの組み合わせを1組ずつ記述せずとも、

=SUMPRODUCT(B12:H12*$B$3:$H$3)

とすれば、セル範囲で指定する事が出来ます。
　但し、これだけでは""が入力された際にエラーとなってしまいますので、

=SUMPRODUCT((0&B12:H12)*$B$3:$H$3)

の様にする事で、B12:H12のセル範囲の中に""が入っているセルが存在していてもエラーとはならずに計算を行う様にする事が出来ます。
　但し、こちらの方は""が入力されている場合限定であり、""ではない普通の文字列データが入力された際にはエラーが出ます。

　尤も、幾ら上記の関数で計算が出来る様になったとしましても、""が残っていたままでは、後から別の関数を付けたす場合や、別のマクロでEnd(xlUp)などを使用して最終行を求める場合などに、思わぬ悪影響が出る事になり、しかも見かけは空欄のセルにしか見えないため、トラブルが起きた際の原因究明が困難になる恐れもありますので、やはり""は全て無くしてしまうようにした方が良いかも知れません。

お礼

kagakusukiさん、ありがとうございます。

=SUMPRODUCT((0&B12:H12)*$B$3:$H$3)
こんな関数があったんですね！
で、０＆とすることで空欄のエラー回避となるのですね。

あと、SUM関数で空欄のエラー回避ができると聞いていたのですがすべての数値につけるのですね。
どおりで、エラーになるわけです。
大変勉強になりました。

「””」のかわりに、「０」ということですが、見た目ごちゃごちゃしていて
たんなる私の好みで空欄になるようにしてみました。
どうしても無理だったら０表示させようかと思っていました。

今のところ、関数で希望のものができるのですが、やはり、後々悪影響がでないように、空欄になるマクロを利用させていただきます。

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

回答No.9

FEX2053

>>"を""で書きなさいってのは、この「式」に対して適用される話なので、
>>左と右で適用できる変数が違ってくるんで、コピーは不可能なんです。

>がわからなくて、悩んでいます。

では

Temp = sh2.Range("B9").value
Msgbox Temp
sh1.Range("A35").End(xlUp).Offset(1).value = Temp

として、中間の結果を確認してみてください。
で、その中間の結果をどこかに

sh1.Range("X1").value = Temp

や

sh1.Range("X1").Formula = Temp

として貼り付けてみてください。そうすると、どういう動きをしてるかわかる
と思いますよ。プログラムはそういった「途中でどうなってるか」を逐次
確認するのがキモです。

実行は[F8]でステップ実行し、貼り付け直後に止めてしまえば問題ない
ですよね。VBAはこうやって中間結果を見ながら作業できるのがいいん
です。

まさか、ステップ実行せず、いきなり初めから動かしたりしてませんよね？

補足

FEX2053さん、ありがとうございます。

もちろん、いきなり初めから動かしてました。。。

こんなふうにステップ実行するのですね。
すごく勉強になりました。
どの数字をコピーしたのかもわかり、納得できました。

ただ、「””」は、表示されなくて、空白に見えるからやっかいです。

回答No.8

kagakusuki

済みません。
　回答No.7で提示させて頂いた方のVBAは間違っておりました。

>sh1.Range("A35").End(xlUp).Offset(0, 1)
　　　～
>sh1.Range("A35").End(xlUp).Offset(0, 7)

に関しては、""が貼り付けられていても構わない、という条件に基づいて、sh2.Range("F13:F19")の値を一気に貼り付ける場合には、やはり[形式を選択して貼り付け]を使った方が良いようです。(処理対象が7セル程度ですので、処理速度の点でFor～Nextを使用するのとどちらが早いのかは微妙ですが)

Sub 転記_条件緩和()

'QNo.8973647VBAでセルのコピーをすると、エラーになる

Dim sh1, sh2 As Worksheet
Dim myValue As Variant

Set sh1 = Sheets(month(Now()) & "")
Set sh2 = Sheets("差出票")

With sh1.Range("A35")
myValue = sh2.Range("B9").Value
If IsError(myValue) Then myValue = Empty
If Len(myValue) > 0 Then .End(xlUp).Offset(1).Value = myValue
sh2.Range("F13:F19").Copy
.End(xlUp).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
End With
Application.CutCopyMode = False

End Sub

補足

kagakusukiさん、お返事が遅れてすみません。
差出票!B9セルには、日付が入っています。
このファイルは、後納郵便を出すときに、
シート「5月」＝3行目に日付、宛先、料金、担当者、内容物が入っています。Iには、当日郵送するレターの料金を表示する関数を入れました。
=IF(B10=差出票!$B$9,'5月'!D10,0)
シート「差出票」＝当日郵送するレターの数を表示し、郵便局へ出します。F13セルに
=IF(COUNTIF('5月'!I:I,E13)=0,"",COUNTIF('5月'!I:I,E13))
シート「5」＝3列目に日付、82、92、120・・・と普通郵便の料金を表示しています。

なので、郵便物がない日は、マクロを動かさないので、差出票!B9セルには必ず日付が入っています。

シート「5」のB4からJ34までは郵便の数が入るので、「””」が入っているとその後の計算に支障があります。
=SUM(B12*$B$3,C12*$C$3,D12*$D$3,E12*$E$3,F12*$F$3,G12*$G$3,H12*$H$3)
という式をL12に入れています。

私の返事が遅れたため、せっかく考えていただきましたがこちらのマクロでは動かないです。
申し訳ございません。

回答No.7

kagakusuki

ちょと気になっている事があるのですが、マクロで処理する前の状態において、A35セルから上にあるセルの内、データが入力されている一番下のセルが例えばA20セルであったとして、差出票!B9セルに何か値が入力されていた場合には、

sh1.Range("A35").End(xlUp).Offset(1) = sh2.Range("B9")

に相当する処理を行いますと、新たにA21セルが一番下のセルという事になりますので、差出票!F13:F19の値を貼り付けるセルはB21:H21のセル範囲という事になります。
　それに対して、差出票!B9セルが空欄又は""であった場合には、一番下のセルはA20のまま変わりませんので、差出票!F13:F19の値を貼り付けるセルはB20:H20のセル範囲という事になります。
　その様な処理をしたいという事なのでしょうか?
　それとも、マクロによる処理を行う前の一番下のセルが例えばA20セルであった場合には、差出票!B9セルが空欄であるか否かには関係なく、必ずB21:H21のセル範囲に差出票!F13:F19の値を貼り付けるという事なのでしょうか?

　どちらなのか良く解りませんでしたが、御質問文に書かれているVBAの処理が「差出票!B9セルが空欄であるか否かによって、貼り付け先が変わる」様な形式となっていたため、取り敢えず回答No.5のマクロでも、そうなる様にしております。
　ですからもし、マクロによる処理を行う前の一番下のセルが例えばA20セルであった場合には、差出票!B9セルが空欄であるか否かには関係なく、必ずB21:H21のセル範囲に差出票!F13:F19の値を貼り付ける様にされたい場合には、その旨を御知らせ願います。

　それと、おそらくは心配する様な事ではないかも知れませんが、

>そもそも””なら貼り付けないか

という処理の仕方ですと、万が一、マクロで値の貼り付けを行う前に、貼り付け先のセルに何か値が入力されていた場合には、貼り付け先のセルに古いデータが残ったままになる恐れがありますので注意して下さい。

　後それから、もしも

>「””」を本当の空欄にする

という処理が必要になるのは

sh1.Range("A35").End(xlUp).Offset(1)

だけであり、

sh1.Range("A35").End(xlUp).Offset(0, 1)
　　　～
sh1.Range("A35").End(xlUp).Offset(0, 7)

に関しては、""が貼り付けられていても構わない、などという"条件であれば"、VBAをもっと単純かつ高速にする事が出来ます。

Sub 転記_条件緩和()

'QNo.8973647VBAでセルのコピーをすると、エラーになる

Dim sh1, sh2 As Worksheet
Dim SN As String
Dim myValue As Variant

SN = Month(Now())
Set sh1 = Sheets(SN)
Set sh2 = Sheets("差出票")

With sh1.Range("A35")
myValue = sh2.Range("B9").Value
If IsError(myValue) Then myValue = Empty
If Len(myValue) > 0 Then .End(xlUp).Offset(1).Value = myValue
.End(xlUp).Offset(0, 1) .Resize(1,7).Value = sh2.Range("F13:F19").Value
End With

End Sub

回答No.6

FEX2053

>sh1.Range("A35").End(xlUp).Offset(1) = sh2.Range("B9")

このやり方のコピーでやってることは、変数Tempを介して

>Temp = sh2.Range("B9").value
>sh1.Range("A35").End(xlUp).Offset(1).value = Temp

ってことです。要は「値貼り付け」なんですね。
で、本来、式をセルに貼り付けるには

>sh1.Range("A35").End(xlUp).Offset(1).Formula = 式

って処理が必要なんです。
"を""で書きなさいってのは、この「式」に対して適用される話なので、

>sh1.Range("A35").End(xlUp).Offset(1) = sh2.Range("B9")

このやり方では、左と右で適用できる変数が違ってくるんで、コピーは
不可能なんです。

なので、原点に戻ってコピーコマンドを使って

sh1.Range("A35").End(xlUp).Offset(1).Copy sh2.Range("B9")

とすれば動くんではないかと思います。この場合、元の式は↓のままで
正解です。だって「Excelの機能を使ってコピーをやってる」んですから。

=IF(COUNTIF('5月'!B4:I13,E13)=0,"",COUNTIF('5月'!I:I,E13))

ちなみに、ちゃんとしたコピーについてはこちらを。
http://officetanaka.net/excel/vba/cell/cell09.htm

補足

FEX2053さん、ありがとうございます。

>sh1.Range("A35").End(xlUp).Offset(1).Copy sh2.Range("B9")
ではなくて、
sh2.Range("B9").Copy sh1.Range("A35").End(xlUp).Offset(1)
なのですが、値の貼付けではなく、すべて貼付けになってしまいました。
それはいいのですが、

>"を""で書きなさいってのは、この「式」に対して適用される話なので、
>左と右で適用できる変数が違ってくるんで、コピーは不可能なんです。

がわからなくて、悩んでいます。
””がどこにもない？
変数が違ってくるというのもよくわからないです。コピーできているので・・・。

超初心者ですみません。

回答No.5

kagakusuki

確認したいのですが、

>値が「0」の場合

の「値」とは、

COUNTIF('5月'!B4:I13,E13)

の部分の計算結果の事であって、

=IF(COUNTIF('5月'!B4:I13,E13)=0,"",COUNTIF('5月'!I:I,E13))

という関数全体の計算結果が0という意味では無い訳ですよね?
　そして、

COUNTIF('5月'!B4:I13,E13)

の部分の計算結果が0だった場合には、元のセルの値が""という「文字数が0の文字列データ」となっていて、本当の意味での空欄ではなくなっているため、後で別の処理を行う際に、.End(xlUp)などの類の処理を行うと正しい最終行が得られないといった類の問題があるので、本当の空欄にするにはどうすれば良いのかというのがこの質問の趣旨ではないかと思います。

　次の様にされては如何でしょうか。

Sub 転記_改()

'QNo.8973647VBAでセルのコピーをすると、エラーになる

Dim sh1, sh2 As Worksheet
Dim SN As String
Dim CopyCell, PasteCell As Range
Dim i As Long
Dim myValue As Variant

SN = Month(Now())
Set sh1 = Sheets(SN)
Set sh2 = Sheets("差出票")

myValue = sh2.Range("B9").Value
Set PasteCell = sh1.Range("A35").End(xlUp).Offset(1)
GoSub label1
With sh1.Range("A35").End(xlUp)
For i = 1 To 7
myValue = sh2.Range("F12").Offset(i).Value
Set PasteCell = .Offset(0, i)
GoSub label1
Next i
End With

Exit Sub

label1:
If IsError(myValue) Then myValue = Empty
If Len(myValue) = 0 Then myValue = Empty
PasteCell.Value = myValue
Return

End Sub

お礼

以下の内容で補足します。よろしいですか？
kagakusukiさん、今回もありがとうございます。
前回は、途中で締め切られてしまい、ベストアンサーにできなくてすみません。

今回の私のつたない説明で理解していただきありがとうございます。
おっしゃるとおりです。
なので、マクロもばっちりスムーズに動きました。

回答No.4

FEX2053

私の下手な説明より、こちらの方が的確に説明できていると思います。
ぜひご一読ください。
http://officetanaka.net/excel/vba/tips/tips90.htm

たぶん、'をどう見てるかの問題ではないかと・・・。

ちなみに上記サイトは本当にいろいろタメになるので、ぜひ通読を。

補足

FEX2053さん、サイト見ました。
たぶん、理解できたとおもうのですが、・・・=0,"""",・・・ではできませんでした。
FEX2053さんはできたのですよね？
こちらの方法で空欄になれば一番うれしいのですが、これ以上の説明は難しいでしょうか？
=IF(COUNTIF('5月'!B4:I13,E13)=0,"",COUNTIF('5月'!I:I,E13))ですが
=IF(COUNTIF('5月'!I:I,E13)=0,"",COUNTIF('5月'!I:I,E13))の間違いでした。
すみません。

回答No.3

FEX2053

=IF(COUNTIF('5月'!B4:I13,E13)=0,"",COUNTIF('5月'!I:I,E13))

こういつは、こうすれば動きます。

=IF(COUNTIF('5月'!B4:I13,E13)=0,"""",COUNTIF('5月'!I:I,E13))

.Formulaで貼り付けるときは、"で文字列の終わりと判断してしまうので
そういう結果になるんです。回避するには"の代わりに""を使ってください。
なので=""ではなく、=""""になるんです。

補足

FEX2053さん、ありがとうございます！
こういう素敵な方法があるのですね！

でも、「”」がひとつ表示されてしまいます。
書いていただいた関数をそのままコピペしたのですが・・・。
なぜだかお分かりになりますか？
よろしくお願いします。

回答No.2

keithin

＞「””」を本当の空欄にするには

そもそも””なら貼り付けないか，あるいはご相談で書かれてるように貼り付けてしまったあとでどうかしたいなら，削除するしかありませんね。

if sh2.range("B9").value <> "" then sh1.Range("A35").End(xlUp).Offset(1) = sh2.Range("B9")
　以下同じ

お礼

keithinさん、希望通りに動きました♪
ありがとうございました。

回答No.1

noname#207526

それは、数式をコピーしているからです。違う結果を求めるならば、編集から値をコピーなどの特殊コピーをするべきでしょう。

補足

alsranさん、早速のお返事ありがとうございます。

http://officetanaka.net/excel/vba/cell/cell09.htm
上記のHPより、
Sub Sample4()
Range("C2") = Range("A1")
End Sub

で、値の貼り付けができるとありました。
「””」は値です。
数式ではないとおもうのですが？
初心者の為、失礼がありましたらお許しください。