Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちらをご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

10171 / 13644 ﾂﾘｰ

←次へ　｜　前へ→

▼

【23267】空白セルを詰めてコピーについて 浜口 05/3/17(木) 21:30

【23271】Re:空白セルを詰めてコピーについて ichinose 05/3/17(木) 23:37

【23274】Re:空白セルを詰めてコピーについて 浜口 05/3/18(金) 1:41

【23275】Re:空白セルを詰めてコピーについて ichinose 05/3/18(金) 7:27

【23276】Re:空白セルを詰めてコピーについて　追伸 ichinose 05/3/18(金) 7:57

【23346】Re:空白セルを詰めてコピーについて　追伸 浜口 05/3/19(土) 2:09


	【23267】空白セルを詰めてコピーについて

浜口 - 05/3/17(木) 21:30 -


	引用なし

	パスワード

はじめまして。会社の日常業務効率化のために
エクセルＶＢＡと悪戦苦闘している、初心者です。
参考書等を見ながら、がんばっているのですが、
どうしても行き詰ってしまい、思い切って質問投稿しました。

どなたか、ご指南いただけないでしょうか？

※質問の要点
（空白セルを詰めてコピーする方法）
：問題点・・関数式で得られた「空白セル」は空白ではないのか？：

：参考例：
　　　注：A列の式はそのままE列まで行固定で入っています。

A1行セル範囲（A1:E1)の値（1,2,0,3,4）←計算式で得られているとする。
A2行セル範囲（A2:E2)の値（1,2, ,3,4）←関数=IF(A1=0,"",A1)で得られた値

上二つの行から、

セル範囲（A4:E4)の値を(1,2,3,4, )となるようにコピーしたいのですが、
　　：A2行・・IF関数が入力されているために、一旦、
　　：A3行にＶＢＡで値のみをコピーする。
その結果

A3行セル範囲（A3:E3)の値（1,2, ,3,4）←値のみを貼り付けて得られた値

A3行をコピーし、それを基にSpecialCells(Type:=xlCellTypeConstants)で
試行しましたが、結果は

A4行セル範囲（A3:E3)の値（1,2, ,3,4）となり、
スッテプインにて調べた結果、
選択範囲A3行が　（A1:A2)(A4:A5)とならずに、
　　　　　　　　（A1:A5)のままでした。

※VBA記述は、以下の通りです。

Range("A2:E2").Copy
　　Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
　　　　 :=False
Dim rg As Range
　　Set rg = Range("A3:E3")
　　Set rg = rg.SpecialCells(Type:=xlCellTypeConstants)
　　rg.Copy
　　Range("A4").PasteSpecial
　　End Select

どうか、よろしくお願いいたします。
記述が下手ですみません。


	【23271】Re:空白セルを詰めてコピーについて

ichinose - 05/3/17(木) 23:37 -


	引用なし

	パスワード

▼浜口さん：
こんばんは。

>※質問の要点
>（空白セルを詰めてコピーする方法）
>：問題点・・関数式で得られた「空白セル」は空白ではないのか？：
>
>：参考例：
>　　　注：A列の式はそのままE列まで行固定で入っています。
>
>A1行セル範囲（A1:E1)の値（1,2,0,3,4）←計算式で得られているとする。
>A2行セル範囲（A2:E2)の値（1,2, ,3,4）←関数=IF(A1=0,"",A1)で得られた値
>
>上二つの行から、
>
>セル範囲（A4:E4)の値を(1,2,3,4, )となるようにコピーしたいのですが、
>　　：A2行・・IF関数が入力されているために、一旦、
>　　：A3行にＶＢＡで値のみをコピーする。
>その結果
>
>A3行セル範囲（A3:E3)の値（1,2, ,3,4）←値のみを貼り付けて得られた値
>
>A3行をコピーし、それを基にSpecialCells(Type:=xlCellTypeConstants)で
>試行しましたが、結果は
>
>A4行セル範囲（A3:E3)の値（1,2, ,3,4）となり、
>スッテプインにて調べた結果、
>選択範囲A3行が　（A1:A2)(A4:A5)とならずに、
>　　　　　　　　（A1:A5)のままでした。
>
'===================================
Sub test()
　 Dim ans As Range
　 On Error Resume Next
　 Set ans = Range("a2:e2").SpecialCells(xlCellTypeFormulas, xlNumbers)
　 If Err.Number = 0 Then
　　　ans.Copy
　　　Range("a4").PasteSpecial xlPasteValues
　　　Application.CutCopyMode = False
　　　End If
　 on error goto 0
End Sub
これで確認してみて下さい。


	【23274】Re:空白セルを詰めてコピーについて

浜口 - 05/3/18(金) 1:41 -


	引用なし

	パスワード

▼ichinose さん：お返事有難うございます。
>'===================================
>Sub test()
>　 Dim ans As Range
>　 On Error Resume Next
>　 Set ans = Range("a2:e2").SpecialCells(xlCellTypeFormulas, xlNumbers)
>　 If Err.Number = 0 Then
>　　　ans.Copy
>　　　Range("a4").PasteSpecial xlPasteValues
>　　　Application.CutCopyMode = False
>　　　End If
>　 on error goto 0
>End Sub
>これで確認してみて下さい。

試してみましたが、やはりダメでした。

ちなみに、ご伝授の式にある[Err.Number = 0 ]というのは
どんなエラーの場合なのでしょうか？
素人ですみません。
というのも、ご伝授の式ですと
エラー０でない場合は、処理を中止するという意味ではないでしょうか？

色々試行錯誤してますが、
どうも、関数式で得られた空白セルから
コピー　⇒　値のみ貼り付け
ですと、
空白セルとは、認知していないようです。
何か方法が、他にないでしょうか？

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


	【23275】Re:空白セルを詰めてコピーについて

ichinose - 05/3/18(金) 7:27 -


	引用なし

	パスワード

▼浜口さん：
おはようがざいます。
私の方では、Excel2000、Excel2002で確認しましたが、
浜口さんが意図するような結果が表示されています。

>>'===================================
>>Sub test()
>>　 Dim ans As Range
>>　 On Error Resume Next
>>　 Set ans = Range("a2:e2").SpecialCells(xlCellTypeFormulas, xlNumbers)
>>　 If Err.Number = 0 Then
　　　　msgbox ans.address
'　これで変数ansにどんなセル範囲が入っていますか？
'　こちらで確認すると、「$A$2:$B$2,$D$2:$E$2」と表示されます
>>　　　ans.Copy
>>　　　Range("a4").PasteSpecial xlPasteValues
>>　　　Application.CutCopyMode = False
>>　　　End If
>>　 on error goto 0
>>End Sub
>>これで確認してみて下さい。
>
>試してみましたが、やはりダメでした。
>
>ちなみに、ご伝授の式にある[Err.Number = 0 ]というのは
>どんなエラーの場合なのでしょうか？
Set ans = Range("a2:e2").SpecialCells(xlCellTypeFormulas, xlNumbers)

でセル範囲A2:E2の中で数式の結果が数値のセル範囲を取得しています。
Err.Number=0は、エラーが発生せずに正常にセル範囲が取得できた場合だけ
処理を行うようにしています。
エラーになる場合は、A2:E2がすべて""だった場合です。
浜口さんが提示された例だと・・・、
A1:E1が　1,2,0,3,4で
A2:E2の数式が=IF(A1=0,"",A1)と言うことで
結果、1,2,,3,4という表示がされているとの事ですから、
この場合は、変数ansに結果が数値のセル範囲（$A$2:$B$2,$D$2:$E$2）
が格納されるはずなのです。

>素人ですみません。
>というのも、ご伝授の式ですと
>エラー０でない場合は、処理を中止するという意味ではないでしょうか？
>
>色々試行錯誤してますが、
>どうも、関数式で得られた空白セルから
>コピー　⇒　値のみ貼り付け
>ですと、
>空白セルとは、認知していないようです。
>何か方法が、他にないでしょうか？
Excelのバージョンは何でしょうか？
2003だとしたら、確認はしていませんが・・・。


	【23276】Re:空白セルを詰めてコピーについて　追伸

ichinose - 05/3/18(金) 7:57 -


	引用なし

	パスワード

▼浜口さん：
新規ブックに以下のコードをコピーして実行してみて下さい
'========================================================
Sub main()
　 Dim ans As Range
　 Call mk_sample
　 On Error Resume Next
　 Set ans = Range("a2:e2").SpecialCells(xlCellTypeFormulas, xlNumbers)
　 If Err.Number = 0 Then
　　　ans.Copy
　　　Range("a4").PasteSpecial xlPasteValues
　　　Application.CutCopyMode = False
　　　End If
　 On Error GoTo 0
End Sub
'================================================================
Sub mk_sample()
　 Range("a1:e1").Value = Array(1, 2, 0, 3, 4)
　 Range("a2:e2").Formula = "=if(a1=0,"""",a1)"
End Sub

あくまでも新規ブックですよ！！
a4:d4に1,2,3,4と表示されませんか？


	【23346】Re:空白セルを詰めてコピーについて　追伸

浜口 - 05/3/19(土) 2:09 -


	引用なし

	パスワード

▼ichinose さん：
ご返信ありがとうございました。
とりあえず、解決しました。
かなり、ど素人的手法なのですが

※関数式により空白に見えるセルを関数で０に変え、そのセルを空白に変換
という、重ね業で、解決しました。

一応下にVBAを記述しておきます。
　　
　　Dim rng As Range
　　Dim n As String
　　n = 0
　　For Each rng In Range("A3:E3")
　　　　If rng = 0 Then
　　　　　 rng.ClearContents
　　　　End If
　　Next rng
　　
　　'空白セルを詰める
　　
　　Dim rg As Range
　　Set rg = Range("A3:E3")
　　Set rg = rg.SpecialCells(Type:=xlCellTypeConstants)
　　rg.Copy
　　Range("A4").PasteSpecial

この結果、

１行目A1:E1(1,2,3,0,4)←数式により得た値
２行目A2:E2(1,2,3,"",4)←IF($A1="","",$A1)
３行目A3:E3(1,2,3,0,4)←IF($A1="",0,$A1)

VBAにより、３行目を、正式に空白セルとし
３行目A3:E3(1,2,3,"",4)←VBAにて取得した値

４行目A4:E4(1,2,3,4,"")←VBAにてコピーした値

と、なりました。
１行目・２行目は、変更したら業務に支障が生じる為
このような手法を考えてみました。

ICHINOSEさんの、ご指摘手法も
コピーして今度じっくりもう一度試してみます。
おそらく、そちらの方法が簡潔ですものね^^

仕事の関係で、まだ先に進まないといけないので・・・・。
ありがとうございました。

又何かありましたら、質問投稿させていただきたいと思いますので
その際はよろしくお願いいたします。


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

10171 / 13644 ﾂﾘｰ

←次へ　｜　前へ→

ページ：

┃

記事番号：

(SS)C-BOARD v3.8 is Free