Excel VBA質問箱 IV

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

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


10173 / 13646 ツリー ←次へ | 前へ→

【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 -

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

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

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

:参考例:
   注: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行にVBAで値のみをコピーする。
その結果

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行にVBAで値のみをコピーする。
>その結果
>
>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 ]というのは
どんなエラーの場合なのでしょうか?
素人ですみません。
というのも、ご伝授の式ですと
エラー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)
が格納されるはずなのです。

>素人ですみません。
>というのも、ご伝授の式ですと
>エラー0でない場合は、処理を中止するという意味ではないでしょうか?
>
>色々試行錯誤してますが、
>どうも、関数式で得られた空白セルから
>コピー ⇒ 値のみ貼り付け
>ですと、
>空白セルとは、認知していないようです。
>何か方法が、他にないでしょうか?
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 さん:
ご返信ありがとうございました。
とりあえず、解決しました。
かなり、ど素人的手法なのですが

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

一応下に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

この結果、

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

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

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

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

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

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

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

10173 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free