Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2013)
計算式のあるセルを空白として処理したい
投稿日時: 18/01/30 09:13:11
投稿者: ekane

いつもお世話になっております。
下記のような文を教えて貰いながら作成しました
 
セルに計算式が入っていてもゼロと表示されているセルの場合はコピーしないように
したいのですが 結果的に計算式も入っているセルもコピーされてしまいます
 
IF条件で0の場合は実行しないことにしたいのですが
If Cells(4, 6).Value = "" Then これでは(値が0でも)計算式があるセルの場合は
コピーされてしまいます。
 
計算式が入っていても表示が0の場合という場合はどのようにすればよいのでしょうか、
また一つのセルではなく、F4:F39というような範囲の指定の仕方もご教授頂けると幸いです。
 
    Dim sh1 As Worksheet
    Set sh1 = Worksheets("sheet1")
       bb = sh1.Range("c65536").End(xlUp).Row
        k = 3
    For I = 4 To bb
    For j = 1 To sh1.Cells(I, "f") 'Fはコピーする回数の数値のセル
        
     With sh1
     .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
     End With
    k = k + 1
     Next j
     Next I
 
 

回答
投稿日時: 18/01/30 09:46:40
投稿者: Suzu

こんにちは。
 

引用:
IF条件で0の場合は実行しないことにしたいのですが
If Cells(4, 6).Value = "" Then

 
おっしゃっている条件と、記載されている条件式に差異があります。
条件式は、空白文字列の場合です。 「ゼロ」と「空白文字列」は違いますよ。
 
 
引用:
また一つのセルではなく、F4:F39というような範囲の指定の仕方もご教授頂けると幸いです。

 
これは、どの部分に指定する為でしょうか。
 
Range("F4:F39") という事でしょうか?

回答
投稿日時: 18/01/30 10:01:46
投稿者: mattuwan44

とりあえずこんな感じでしょうか?
1行に色々なことを書かずに、
1づつ、手順を書いていけばわかりやすくないですかね?
 
 
Sub test2()
    Dim sh1 As Worksheet '操作対象のシート
    Dim rngTop As Range 'コピー対象セル範囲の始まりのセル
    Dim rngBottom As Range 'コピー対象セル範囲の終りのセル
    Dim rngFrom As Range 'コピー対象セル範囲
    Dim c As Range 'コピーする個々のセル
    Dim ixRow As Long '貼付先の行番号
    Dim v As Variant 'コピーの行数
 
    Set sh1 = Worksheets("Sheet1")
    With sh1
        Set rngTop = .Range("C1")
        Set rngBottom = .Cells(.Rows.Count, "C").End(xlUp)
        Set rngFrom = .Range(rngTop, rngBottom)
    End With
    ixRow = 4
 
    For Each c In rngFrom
        v = c.Offset(, 3).Value
        If IsNumeric(v) = True Then
            If v > 0 Then
                c.Resize(6, 3).Copy sh1.Cells(ixRow, "H").Resize(v * 6, 3)
                ixRow = ixRow + v '次の貼付先先頭行数の用意
            End If
        End If
    Next
End Sub
 
 

引用:
IF条件で0の場合は実行しないことにしたいのですが
If Cells(4, 6).Value = "" Then これでは(値が0でも)計算式があるセルの場合は
コピーされてしまいます。

 
まず数値に変換可能か調べて、変換可能場合に0より大きいか比較するといいのかな?

投稿日時: 18/01/30 10:25:08
投稿者: ekane

 早速のご回答 誠にありがとうございます。
 
Suzu様
 >Range("F4:F39") という事でしょうか?
    ハイそうです
 >条件式は、空白文字列の場合です。 「ゼロ」と「空白文字列」は違いますよ。
   失礼しました了解しました。
 
mattuwan44 様
 
わかりやすいようにありがとうございます。
でもわたしには理解に時間がかかります
 
>まず数値に変換可能か調べて、変換可能場合に0より大きいか比較するといいのかな?
   この部分も勉強しないとわからない部分でお時間(日にち)を頂くことになります。
 
お二人様、もう少しお時間いただくことになると思いますが、またご報告させていただきます
ので宜しくお願い致します。
 
 
 

回答
投稿日時: 18/01/30 10:56:54
投稿者: もこな2

Suzuさんの仰るとおり、質問のポイントがよくわからないです。
もしかして、ゼロ値のセルにゼロを表示するのチェックを外したブックの計算結果が0になる数式が入ってるセルのValueを参照したら0が入ってしまいます。
ってことでしょうか?でもコードを見る限り同じブック内(シート内?)の話のようですから違いますかね。ちょっと謎です。
 
とりあえず、「計算式が入っていても表示が0」っていう条件を詳しく教えて頂いた方が良いかもです。
(たとえば0.1は小数点以下を表示させるようにしていなければ0と表示されますが、エクセル君から見れば0ではありません。)
 
セル範囲は、Suzuさんの方法のほかに、Range(Range("F4"),Range("F39"))っていう方法(範囲の「始セル」と「終セル」を記述)という方法もあります。
※当然ですけど「始セル」と「終セル」は同じシートでないとエラーになります。もっと言えば最初のRangeも同じシートでないとエラーになります。
 
ところで、質問とは関係ないですけど、老婆心ながら、インデント位置直した方がよくないですか?
掲示板に投稿するときにずれたならいいんですけど、投稿の通りで組んでいるならご自身が見づらいと思うんですけど・・
私ならこんな感じにします。(「bb」もめんどうなので直接書いちゃってます)
 
    Dim sh1 As Worksheet
 
    Set sh1 = Worksheets("sheet1")
    k = 3
 
    For i = 4 To sh1.Range("c65536").End(xlUp).Row
        For j = 1 To sh1.Cells(i, "f") 'Fはコピーする回数の数値のセル
            With sh1
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(i, "C").Resize(6, 3).Value
            End With
            k = k + 1
        Next j
    Next i
 

回答
投稿日時: 18/01/30 11:32:58
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
引用:
If Cells(4, 6).Value = "" Then これでは(値が0でも)計算式があるセルの場合は
 コピーされてしまいます。
  
計算式が入っていても表示が0の場合という場合はどのようにすればよいのでしょうか、
また一つのセルではなく、F4:F39というような範囲の指定の仕方もご教授頂けると幸いです。

 

これに関しては、既に、他の回答者からアドバイスがありますが、
掲示されているコードのどこに入れあいのかな?
 
それと
コードにはインデントをキチンとつけましょう。
 
以下は、インデントを付けた例です。コードの内容は変更してありません。
 
    Dim sh1 As Worksheet
  Set sh1 = Worksheets("sheet1")
    bb = sh1.Range("c65536").End(xlUp).Row
    k = 3
    For I = 4 To bb
        For j = 1 To sh1.Cells(I, "f") 'Fはコピーする回数の数値のセル
            With sh1
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
            End With
            k = k + 1
        Next j
    Next I
 
折角、With句を使用しているから、ループの外に出します
オブジェクトへのアクセスはできるだけ少ない方がよい。
ループの中で、With句を記述すると、都度、アクセスが発生します。
 
    Dim sh1 As Worksheet
  Set sh1 = Worksheets("sheet1")
    With Sh1
        bb = .Range("c65536").End(xlUp).Row
        k = 3
        For I = 4 To bb
            For j = 1 To .Cells(I, "f") 'Fはコピーする回数の数値のセル
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
                k = k + 1
            Next j
        Next I
    End With
 
もう一つ、
 >.Range("c65536").End(xlUp).Row
は、
.Range("c" & .Rows.Count).End(xlUp).Row
に変更したほうがよいでしょう。

投稿日時: 18/01/30 13:09:28
投稿者: ekane

もこな2様 WinArrow 様
お手数をお掛け大変ありがとうございます。
インデントの件はそのように致します
 
もこな様
 
「計算式が入っていて表示が0表示」とは
 
現在セルには0と表示されていますが、そのセルには 「=シート名!E22」 の様に他のシートを参照し
更にその シート名!E22のセルには 同じシート内の =SUM(E18:E21)等の式が入っております
(注文数がないため結果が0になっております、sheet1ではこのセルを参照しており表示が0となって
 おります)
 
ご提示いただきましたコードは0となっている商品もコピーされるのですが、この0のセルをコピーした
くないのです。説明下手で申し訳ありません。
 
>Range(Range("F4"),Range("F39"))っていう方法(範囲の「始セル」と「終セル」を記述)という方法も
>あります。
    ありがとうございました。
 
 
 
WinArrow 様
 
>ループの中で、With句を記述すると、都度、アクセスが発生します。
 そうだったんですねmsgboxに表示させたらその現象でした
 
>掲示されているコードのどこに入れあいのかな?
   一番最初になると思いますが、もし注文数がない場合はコピーをしないという方法に
   したいと思っています。(コピー先セルはクリアにする)
 
>.Range("c" & .Rows.Count).End(xlUp).Row  
    ありがとうございます。

回答
投稿日時: 18/01/30 13:50:06
投稿者: WinArrow
投稿者のウェブサイトに移動

>>掲示されているコードのどこに入れあいのかな?
>   一番最初になると思いますが、もし注文数がない場合はコピーをしないという方法に
>   したいと思っています。(コピー先セルはクリアにする)
 
この表現、少しおかしいですが、
↓のFor の行の次に挿入すると思います。
 
 
            For j = 1 To .Cells(I, "f") 'Fはコピーする回数の数値のセル
  If 注文数あり Then
                    .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
                    k = k + 1
                 End If
            Next j
 
注文数のセルはご自分で変更してください。
たぶん、可変となるので、Cells(4, 6) を変更することになると思います。
 
 
※おかしな表現
>(コピー先セルはクリアにする)
この表現は不要と思いますが・・・
 
 
あと、セル範囲に関する質問がありましたが、
それは、どこで使おうと考えているんですか?
 
 

回答
投稿日時: 18/01/30 14:10:31
投稿者: WinArrow
投稿者のウェブサイトに移動

数式の結果を、空白文字列にする数式の例
=IF(A1>=100,100,"")
の数式は、セルA1の「値」が100未満の場合、「空白文字列」となります。
この数式がセルB1に入力されていたとすると
If Range("B1").Value = "" Then
で、空白文字列を判断することができます。
 
If Range("B1").Value = 0 Then
と記述すると、不一致となってしまいます。
 
両方を判別したいときは、
If Range("B1").Value = "" Or Range("B1").Value = 0 Then
と記述する方法があります。
 
セルB1が数式であるという保証されている場合だけです。
 
次にセル範囲で「""」「0」を判断する場合は、
その全部が「""」「0」である場合は、SUM関数で合計してみれば判断できます。
If WorksheetFunction.Sum(Range("B1:B10")) = 0 Then MsgBox "OK"
SUM関数は、空白文字列や数字以外を「0」と見做してくれます。
実運用では、英字や日本語などの文字列が入力されないという前提で使わないと
思わぬ結果が待っているかもしれませんので、注意が必要です。
一部のセルに関してチェックしたい場合は、
ループする方が分かりやすいでしょう。
 
 
 
 

回答
投稿日時: 18/01/30 14:16:58
投稿者: もこな2

なるほど。。。
 
ちなみに、「=シート名!E22」を「=IF(シート名!E22=0,"",シート名!E22)」に変えちゃうってのはダメなんでしょうか?
こうすれば、0文字の文字列がコピーされることになるので結果として0っていう値はコピーされなくなります。
 
そうでなければ、コピーした後で値が0だったらクリアするとかでしょうか。。。
WinArrowさんが整理されたコードに赤字を追加
(なんか回りくどくなった&範囲あってるのか自信なし)
 
    Dim tmp As Range, buf As Range
    Dim sh1 As Worksheet
    Set sh1 = Worksheets("sheet1")
    With sh1
        bb = .Range("c65536").End(xlUp).Row
        k = 3
        For i = 4 To bb
            For j = 1 To .Cells(i, "f") 'Fはコピーする回数の数値のセル
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(i, "C").Resize(6, 3).Value
                k = k + 1
            Next j
        Next i
 
        For Each tmp In .Range(.Cells(4, "K"), .Cells(k + 6, "K"))
            If tmp.Value = 0 Then
                If buf Is Nothing Then
                    Set buf = tmp
                Else
                    Set buf = Union(buf, tmp)
                End If
            End If
        Next tmp
 
        If Not buf Is Nothing Then buf.ClearComments

 
    End With
 
テストしてないので上手く動かないかも。
 

回答
投稿日時: 18/01/30 14:53:33
投稿者: WinArrow
投稿者のウェブサイトに移動

> .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
この複写元セル範囲の中に「0」(数式の結果)のセルがあり、
それを複写先セルでは、空白((または、空白文字列)にしたい
ということでしょうか?
 
若し、そうであれば、方法はつ
(1)Resizeを使ってセル範囲で複写せず、ループさせてセル1つづつ複写する。
  その時、「0」判断して「0」は複写しない
 
(2)複写後、ループで「0」のセルをクリアする
 
(3)複写元のセルの数式を変更し、条件w追加して、結果が「0」になる場合は「""」にする。
  どんな数式が入っているのかわかりませんが、この方法が不明ならば、
  具体的な数式を掲示して質問してください。
 
私見ですが、レスポンスの関係で(1)(2)より、(3)をお勧めします。
 
 

投稿日時: 18/01/30 15:04:11
投稿者: ekane

   WinArrow様本当に申し訳けありません
  追加してみました。
 
 Dim sh1 As Worksheet
    Set sh1 = Worksheets("鍋焼・本数・袋数")
    With sh1
        bb = .Range("c65536").End(xlUp).Row
        k = 3
        For I = 4 To bb
      ---------------------------
    Dim sh1 As Worksheet
    Set sh1 = Worksheets("鍋焼・本数・袋数")
    With sh1
        bb = .Range("c65536").End(xlUp).Row
        k = 3
        For I = 4 To bb
                For j = 1 To .Cells(I, "f") 'Fはコピーする回数の数値のセル
                      
    '---------------------ここに挿入しました
    If Cells(4, 6) = 0 Then '数値が入っていなかったらの式がわかりません
            
    '前回の計算して表示されている数値がクリアになりません
       Range("h4:i39").ClearComments
 
        Else '( Elseの記入場所はここでよろしいでしょうか)
                   'そうでなければ下記を実行
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
                    k = k + 1
     End If
            Next j
      '---------------------
            Next I
    End With
   Next I
    End With
>あと、セル範囲に関する質問がありましたが、それは、どこで使おうと考えているんですか
   注文数量が入力されている範囲 Cells(4, 6)ですが とりあえず次回とさせていただきます。
 
>.Range("c" & .Rows.Count).End(xlUp).Rowまだ修正してありませんすみません。

回答
投稿日時: 18/01/30 15:20:55
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
'---------------------ここに挿入しました
    If Cells(4, 6) = 0 Then '数値が入っていなかったらの式がわかりません
             
    '前回の計算して表示されている数値がクリアになりません
       Range("h4:i39").ClearComments
   
         Else '( Elseの記入場所はここでよろしいでしょうか)
                    'そうでなければ下記を実行
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
                     k = k + 1
      End If
             Next j
       '---------------------

 
エッ?
 
>If Cells(4, 6) = 0
この時のセルは、どこのシートを参照しようとしているんですか?
しかも、常に固定(F6)のセルだし・・・・
複写元のセルの「値」が「0」以外だったら複写するのではないのですか?
この時の条件で複写元セルって、どこのセルを参照するかは、回答者には分かりません。
 

回答
投稿日時: 18/01/30 15:27:35
投稿者: WinArrow
投稿者のウェブサイトに移動

最初の質問に戻りますが
あなたは、
>セルに計算式が入っていてもゼロと表示されているセルの場合はコピーしないようにしたい
という考えは変わっていませんか?
 
この仕様に基づいて
>計算式が入って
いるセルは、どこなんですか?
 
Cells(4, 6)ではなく、
For ループの構造を頭にいれて、
相対位置で答えてください。

回答
投稿日時: 18/01/30 15:31:49
投稿者: もこな2

まぁとりあえず。。18/01/30 14:16:58投稿したコードについて修正します。
 
効率はよくないけど、こちらのほうが見やすい(わかりやすい)かもです。
 
    Dim tmp As Range
    Dim sh1 As Worksheet
    Set sh1 = Worksheets("sheet1")
    With sh1
        bb = .Range("c65536").End(xlUp).Row
        k = 3
        For i = 4 To bb
            For j = 1 To .Cells(i, "f") 'Fはコピーする回数の数値のセル
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(i, "C").Resize(6, 3).Value
        For Each tmp In .Cells(k + 1, "H").Resize(6, 3)
                    If tmp.Value = 0 Then tmp.ClearComments
                Next tmp

                k = k + 1
            Next j
        Next i
     End With
 
ちなみに、これループの中でSTEP 1 だから参照先も参照元も範囲かぶりますよね
私はなんかもやもやしますが、質問者さん的には気にならないんでしょうか?

回答
投稿日時: 18/01/30 15:34:13
投稿者: WinArrow
投稿者のウェブサイトに移動

複写後、複写先セルをクリアするように見受けられますが、
複写先セルの先頭行を「K」で計算しているので
セル範囲だけクリアしても、「K」の値は変わっていないので
空白行ができてしまいますよ!

投稿日時: 18/01/30 15:48:37
投稿者: ekane

もこな2様
ありがとうございます
 
>=IF(シート名!E22=0,"",シート名!E22)」
  でもコピーされてしまいます。
 
>そうでなければ、コピーした後で値が0だったらクリアするとかでしょうか
  結果的にこうするしかないのかなと思います
 
>私はなんかもやもやしますが、質問者さん的には気にならないんでしょうか?
 そもそも基本がわかっていないのですみません。
 
返信しようと思って時間を取っている間にご回答くださいましてありがとうございます

回答
投稿日時: 18/01/30 16:13:47
投稿者: WinArrow
投稿者のウェブサイトに移動

数式が入っているセルを説明してもらうのが前提ですが、
resizeで範囲指定しているセルに数式が入っているとして、
For ループで対応します
 
    Dim sh1 As Worksheet,R as LOng,C As Long
  Set sh1 = Worksheets("sheet1")
     With Sh1
         bb = .Range("c65536").End(xlUp).Row
         k = 3
         For I = 4 To bb
             For j = 1 To .Cells(I, "f") 'Fはコピーする回数の数値のセル
 ' .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(I, "C").Resize(6, 3).Value
                 For R = 1 To 6
                    For C = 1 To 3
                       If .Cells(I + R -1, 3 + C - 1).Value <> 0 Then
                           .Cells(K, "H").Offset(R, C -1).Value = .Cells(I + R -1, 3 + C - 1).Value
                       End If
                    Next
                Next
                k = k + 1
             Next j
         Next I
     End With
  

回答
投稿日時: 18/01/30 16:32:55
投稿者: もこな2

ekane さんの引用:
>私はなんかもやもやしますが、質問者さん的には気にならないんでしょうか?
 そもそも基本がわかっていないのですみません。
あっいや、私の勘違い(コード読み間違い)かもしれないですけど・・・
For j = 1 To 〜 の部分みると
 1巡目 H4:K10 ← C4:F10
 2巡目 H5:K11 ← C4:F11
 3巡目 H6:K12 ← C4:F12
となるとおもうので、エクセル君がツッコミ担当だったら
 >2巡目の段階で、5〜10行目ってさっきやったやん!
 >3巡目の段階で、6〜10行目またやるんかい! 11行目も2回目やん
ってツッコミ入れてきそうな気がします。
 
で、こう整理してみると、変数「j」と変数「k」の役割って何です?
同じ行処理するなら変数「i」だけで対応できますよね?
更に言えば、Resizeで行方向に6広げてるのは何を狙ってるんですか?(STEP6するなら解りますけど)
 
ってことが気になります。
まぁ他の回答者さんが突っ込んでないので単純に私がちょっと勘違いしてるだけかもしれないですが・・

投稿日時: 18/01/30 16:35:57
投稿者: ekane

 WinArrow 様
お手数をお掛け致しております。
 
>複写後、複写先セルをクリアするように見受けられますが、
   注文数がなかった場合に前日残っている複写先のセル範囲をクリアするつもりです。
 
>空白行ができてしまいますよ!
   確かに空白行ができた時がありました
 
>セルに計算式が入っていてもゼロと表示されているセルの場合はコピーしないようにしたい
>という考えは変わっていませんか?
 
最終的には0もコピーされてもいいかともおもいます。ただ注文の無い0の商品は表示させない方が印刷
した場合に良いかと思ったまでした。
  
>この仕様に基づいて
>計算式が入って いるセルは、どこなんですか?
  
>Cells(4, 6)ではなく、
>For ループの構造を頭にいれて、相対位置で答えてください。
 
 この部分も理解不足ですがRange("d4:D39")に入っております
 このセルは 「=シート名!E22」 他のシートを参照し そのセルE22には
 =SUM(E18:E21)等の式が入っております
 
  混乱しており時間がかかり申し訳けありません。  
 

回答
投稿日時: 18/01/30 16:53:28
投稿者: もこな2

失礼しました。Resizeの件ちょっと範囲が違いますね
 1巡目 H4:J9  ← C4:E9
 2巡目 H5:J10 ← C4:F10
 3巡目 H6:J11 ← C4:F11
こうですね。それでも行がかぶってるような。。。。
あんまし自信ないので、他の回答者さんのアドバイスを参考にしてください。

投稿日時: 18/01/30 17:05:35
投稿者: ekane

もこな2 様
ありがとございます。
ご指摘頂いだループが問題かも知れません今までご教授下さったコードで何故か3行分だけ
余計にコピーされていました。
この余計部分がコピーされなければ0表示セルもコピーされないかと思っています。
 
>変数「j」と変数「k」の役割って何です?
   jはコピーする回数です
  kは単純にコピーする先の指定セルと思っております
ご指摘を勉強させていただきます。
 
 

回答
投稿日時: 18/01/30 17:09:25
投稿者: WinArrow
投稿者のウェブサイトに移動

ekane さんの引用:
WinArrow 様
お手数をお掛け致しております。
 
>複写後、複写先セルをクリアするように見受けられますが、
   注文数がなかった場合に前日残っている複写先のセル範囲をクリアするつもりです。

 
今迄の話の中に
>前日残っている複写先のセル範囲をクリアする
なんて、話なかったよね?
 
ふつうは、処理の最初に複写先エリアを無条件でクリアするものです。
 
>注文数がなかった場合に
この表現もあいまいです。
まず、注文数の場所(セル)の説明が無い・・・何回質問しても回答なし。
 
ekane さんの引用:

>空白行ができてしまいますよ!
   確かに空白行ができた時がありました
 
>セルに計算式が入っていてもゼロと表示されているセルの場合はコピーしないようにしたい
>という考えは変わっていませんか?
 
最終的には0もコピーされてもいいかともおもいます。ただ注文の無い0の商品は表示させない方が印刷
した場合に良いかと思ったまでした。

注文のないことと注文数=0とは同じ意味ですか?
注文に無い商品は印刷しないほうgよいでしょう。
だから、注文数が、どこのセルに入っているんですか?
 
基本的に、このプログラムおかしくないですか?
 
C列セルにデータが入っている件数が処理対象となっているが、
たとえば、セルC4〜E4に1件だけのデータの場合、
 
複写する仕様
>.Cells(k + 1, "H").Resize(6, 3).Value = .Cells(i, "C").Resize(6, 3).Value
だから、下に6行、右に3列・・・・つまり、C4:E10が複写元になります。
これって、おかしくない?

回答
投稿日時: 18/01/30 17:23:00
投稿者: もこな2

引用:
=IF(シート名!E22=0,"",シート名!E22)」
  でもコピーされてしまいます。

 こちらもなんででしょうね・・・
  
ちょっと別ブックで以下のテストをしてみてください。
(1)Sheet1のA1〜C3に以下のように入力
   0 2 3
   4 5 0
   7 0 9
  
(2)Sheet2のA1に以下の数式を入力
=IF(Sheet1!A1=0,"",Sheet1!A1)
  
(3)Sheet2のA1をコピーして、そのまま同じシートのA1〜C3に貼付
  
(4)標準モジュールを追加して、以下のコードを記述&実行
Sub test()
    Worksheets(2).Range("D5").Resize(3, 3).Value = _
    Worksheets(2).Range("A1").Resize(3, 3).Value
End Sub
  
私の環境(Excel2013)だと、0にはならず、ちゃんとブランクがコピーされてるんですよね。。

回答
投稿日時: 18/01/30 17:48:50
投稿者: WinArrow
投稿者のウェブサイトに移動

↓のスレと関連があると思いますが、
 
http://www.moug.net/faq/viewtopic.php?t=76723
 
この6行複写は正しいのですか?

回答
投稿日時: 18/01/30 18:07:03
投稿者: WinArrow
投稿者のウェブサイトに移動

立て続けで申し訳ありませんが、
 
 
複写元セルと複写先セルの場所を明確にするために
Debug.Print を挿入して実行してみました。
 
セルC4に適当なデータを入寮
セルF4に「2」を入力
 
 
結果:F4の死体通り2回実行されています。
【1巡目】 $H$4:$J$9←$C$4:$E$9
【2巡目】 $H$5:$J$10←$C$4:$E$9
 
 1巡目も2巡目も同じ「値」を複写しているから区別できないかもしれませんが、
 1巡目の複写先領域と2巡目の複写先領域がかぶっています。
 これって、おかしいと思います。
 仕様を見直した方がよいと思います。
 
 
 
 
コードは以下です。
 

   Dim sh1 As Worksheet, bb As Long, i As Long, k As Long, j As Long
    Set sh1 = Worksheets("sheet1")
     With sh1
         bb = .Range("c65536").End(xlUp).Row
         k = 3
         For i = 4 To bb
             For j = 1 To .Cells(i, "f") 'Fはコピーする回数の数値のセル
                Debug.Print "【" & j & "巡目】 " & .Cells(k + 1, "H").Resize(6, 3).Address & "←" & .Cells(i, "C").Resize(6, 3).Address
                .Cells(k + 1, "H").Resize(6, 3).Value = .Cells(i, "C").Resize(6, 3).Value
                 k = k + 1
             Next j
         Next i
     End With

投稿日時: 18/01/30 18:10:01
投稿者: ekane

申し訳ありません
 
>注文のないことと注文数=0とは同じ意味ですか?
   ハイ同じです注文のないセルは0と表示されています。
 
>注文に無い商品は印刷しないほうgよいでしょう。
  ハイ
>だから、注文数が、どこのセルに入っているんですか?
   商品によって違いますが sheet2 や sheet3 など色々なセルにあり
   それをsheet1 のD4〜D39のセルで商品ごとに参照しております。
   (この説明で大丈夫でしょうか)
 
>.Cells(k + 1, "H").Resize(6, 3).Value = .Cells(i, "C").Resize(6, 3).Value
>だから、下に6行、右に3列・・・・つまり、C4:E10が複写元になります。
>これって、おかしくない?
     C4:E39を複写元にしたいです。
本当にお手数をお掛け致します

投稿日時: 18/01/30 18:26:09
投稿者: ekane

もこな2 様
ありがとうございます
ご丁寧にありがとうございます、ちゃんとブランクがコピーされているんですが
コードの中ではブランクもコピーするようになっているかどうかもわかりません。
お手数をお掛け致しております

回答
投稿日時: 18/01/30 18:28:51
投稿者: WinArrow
投稿者のウェブサイトに移動

>   商品によって違いますが sheet2 や sheet3 など色々なセルにあり
>   それをsheet1 のD4〜D39のセルで商品ごとに参照しております。
>    (この説明で大丈夫でしょうか)
 
今回、処理の対象は、「Sheet1」なので、他のシートの話は不要
しかし、D4〜D39セルの数式は、公開して日しいですね?
 
>>.Cells(k + 1, "H").Resize(6, 3).Value = .Cells(i, "C").Resize(6, 3).Value
>>だから、下に6行、右に3列・・・・つまり、C4:E10が複写元になります。
>これって、おかしくない?
> C4:E39を複写元にしたいです。
 
どうも話がかみ合いませんね・・・・
コードそのものを理解していますでしょうか?
 
>.Cells(i, "C").Resize(6, 3).Value
このコードで複写元セル範囲があなたが考えているものとあっているかという質問です。
再確認ですが、なぜ、6行を複写するんですか?
また、F列セルに複写数との関係、目的は?

投稿日時: 18/01/30 19:05:34
投稿者: ekane

お手数をお掛け致し申し訳ありませんありません
 
>コードそのものを理解していますでしょうか?
   申し訳ありません難しい部分多くてわかりません。
  
>再確認ですが、なぜ、6行を複写するんですか?
  意味がわからないのに書いてありますが 6行だけではなくD4〜D39の36行セルを対象にしたいです。
 
>また、F列セルに複写数との関係、目的は?
 
   商品は1回に製造できる量が100なので (例です)注文が360入った場合は
  100を3回と60を1回 廻して4回転させるため4になる式が入っております
  =IF(AND(E4>1,E4<100),1,ROUNDUP(CEILING(E4/100,0.5),0))
 
  その4回の作業ごとに必要な材料を表示するために4回コピーをしています
  一回目から3回目までは同じ分量を使用しますがわかりやすいように3回同じものをコピー
  し作業者が一回ごとに確認しております。
 
 
 

回答
投稿日時: 18/01/30 19:52:39
投稿者: WinArrow
投稿者のウェブサイトに移動

ekane さんの引用:

>コードそのものを理解していますでしょうか?
   申し訳ありません難しい部分多くてわかりません。

全体像を把握あいんしまま、コードを記述することは、
南にいくか北に行くかどちらの方角に行くか?決まっていないまま、
取りあえず、出発することに似ています。
途中で、私はどこに行くのでしょう?って、通りすがりの人に聞いている状態です。
 
ekane さんの引用:

>再確認ですが、なぜ、6行を複写するんですか?
  意味がわからないのに書いてありますが 6行だけではなくD4〜D39の36行セルを対象にしたいです。

意味が分からないのでしたら、意味が分かるまでコードを眺めて勉強しましょう。
コードの内容を理解しないまま、進めてもあなたのためになりません。
 
まず、表の全体像を掲示してください。
複写対象セル(列)には、何が入っているんですか?
 
コードから推察すると、C列〜E列が複写対象列になっているが、
説明の中で「E22」とか「E18:E21」という書いてあったが、それとの関係は?
 
ekane さんの引用:

>また、F列セルに複写数との関係、目的は?
 
   商品は1回に製造できる量が100なので (例です)注文が360入った場合は
  100を3回と60を1回 廻して4回転させるため4になる式が入っております
  =IF(AND(E4>1,E4<100),1,ROUNDUP(CEILING(E4/100,0.5),0))
 
  その4回の作業ごとに必要な材料を表示するために4回コピーをしています
  一回目から3回目までは同じ分量を使用しますがわかりやすいように3回同じものをコピー
  し作業者が一回ごとに確認しております。

 
F列セルは、分かりました。
同じデータを4件複写しておいて、実際の発注数?は人手で決めているということですか?
ところで
この計算式で参照しているE4セルは、注文数ですよね?
↑のほうに、注文数は、D4:D39って、書いてありましたよね?
D列とE列とは、どのような関係ですか?

回答
投稿日時: 18/01/30 19:59:40
投稿者: WinArrow
投稿者のウェブサイトに移動

F列の計算式
難しく考えなくても
↓のような数式でも対応できますよ
=CEILING(E4,100)/100

投稿日時: 18/01/30 20:19:55
投稿者: ekane

WinArrow 様ありがとうございます。
大丈夫でしょうか何もわからないのにこんなにお付き合いいただいて心配しております
 
>同じデータを4件複写しておいて、実際の発注数?は人手で決めているということですか?
  
同じデータですので4件とも同じ数量がコピーされてしまうわけですがこの隣の列に教えて頂いた
 =IF(J4=0,"",IF(H4=H5,MIN(100,J4),IF(MOD(J4,100)=0,100,MOD(J4,100))))という式で
360の場合100づつ引いて表示させております一回目(1行目)から3回目は100で次の4行目は60となります)
 
>ところで
>この計算式で参照しているE4セルは、注文数ですよね?
>↑のほうに、注文数は、D4:D39って、書いてありましたよね?
>D列とE列とは、どのような関係ですか?
 
 C列     D列    E列
 品名    注文数   D列×30としてkgに変換しているセルです(注文数とは1が30kのため)
本当にお手数をお掛けします。
 

投稿日時: 18/01/30 21:04:53
投稿者: ekane

申し訳ありません、全ての範囲です
C列     D列    E列    F列    G列      H  I  J    K
品名    注文数   E列はD列×30でkgに変換(注文数とは1が30kのため) 
            F列は関数でコピー回数を求めています 
            G列は空白   H、I、J はC、D、Eのコピー先です
            K列はE列のコピーがJ列に入るので100単位にまとめる関数が入っております
 
 
                                                 
 

回答
投稿日時: 18/01/30 21:07:33
投稿者: WinArrow
投稿者のウェブサイトに移動

だいぶ、状況がわ駆ってきました。
 
> C列     D列    E列
>  品名    注文数   D列×30としてkgに変換しているセルです(注文数とは1が30kのため)
 
ということでしたら、F列セルの数式はNGですね?
E4セルを参照するのではなく、D4を参照するのでは?
↓の修正したほうがよいでしょう。
=CEILING(D4,100)/100
 
 
元データ:C4〜E列のデータの最終まで
 
複写元:C4:E4 但し、F4>0を対象とする
複写先:H4:J4
    ただし、F4のコピー数分複写する
      
以下、サンプルコードを提示するので、基地と勉強してくださいね・・・・
 
    Dim sh1 As Worksheet, MaxRow As Long, i As Long, k As Long, j As Long
    Set sh1 = Worksheets("sheet1")
     With sh1
         MaxRow = .Range("C" & .Rows.Count).End(xlUp).Row
         k = 3
         For i = 4 To MaxRow
            If .Cells(i, "F").Value > 0 Then
                .Cells(k + 1, "H").Resize(.Cells(i, "F").Value, 3).Value = .Cells(i, "C").Resize(, 3).Value
                If .Cells(i, "F").Value > 1 Then
                    .Cells(k + 1, "J").Resize(.Cells(i, "F").Value - 1).Value = 100
                    .Cells(k + 1, "J").Offset(.Cells(i, "F").Value - 1).Value = .Cells(k + 1, "J").Offset(.Cells(i, "F").Value - 1).Value - _
                                WorksheetFunction.Sum(.Cells(k + 1, "J").Resize(.Cells(i, "F").Value - 1))
                End If
                k = k + .Cells(i, "F").Value
            End If
         Next i
     End With
 
コメント
4件の場合
一旦、H4〜J7に複写sます。
J4:J6の「値」を100に変更
J7−SUM(J4:J6)をJ7を代入(余り)
F4(コピー数)は、注文数が「0」の場合は、当然、コピー数も「0」のはず。
 
 
 
 

回答
投稿日時: 18/01/30 21:16:36
投稿者: WinArrow
投稿者のウェブサイトに移動

E列が注文数と考えていたコードなので、サンプルコード修正です。
 
 
以下、サンプルコードを提示するので、基地と勉強してくださいね・・・・
  
     Dim sh1 As Worksheet, MaxRow As Long, i As Long, k As Long, j As Long
     Set sh1 = Worksheets("sheet1")
      With sh1
          MaxRow = .Range("C" & .Rows.Count).End(xlUp).Row
          k = 3
          For i = 4 To MaxRow
             If .Cells(i, "F").Value > 0 Then
                 .Cells(k + 1, "H").Resize(.Cells(i, "F").Value, 3).Value = .Cells(i, "C").Resize(, 3).Value
                 If .Cells(i, "F").Value > 1 Then
                     .Cells(k + 1, "I").Resize(.Cells(i, "F").Value - 1).Value = 100
                     .Cells(k + 1, "I").Offset(.Cells(i, "F").Value - 1).Value = .Cells(k + 1, "I").Offset(.Cells(i, "F").Value - 1).Value - _
                                 WorksheetFunction.Sum(.Cells(k + 1, "I").Resize(.Cells(i, "F").Value - 1))
                 End If
                 k = k + .Cells(i, "F").Value
             End If
          Next i
      End With
   
コメント
4件の場合
 一旦、H4〜J7に複写sます。
 I4:I6の「値」を100に変更
 I7−SUM(I4:I6)をI7を代入(余り)
 F4(コピー数)は、注文数が「0」の場合は、当然、コピー数も「0」のはず。
  
   
  

投稿日時: 18/01/30 22:40:44
投稿者: ekane

WinArrow 様
本当に本当にありがとうございました。
大変な労力とご指導いただき誠にありがとうございました。
完璧な結果を得ることができました。
ボランティア精神に感謝!言葉だけでは申し訳ない気持ちです。
最初からキチンと全体像を説明申し上げれば良かったのにと思っております。
勉強していきます。
長い時間お付き合い下さいましてありがとうございました。
 
Suzu 様 mattuwan44様  もこな2様 
ありがとうございました。