[[20120726120334]]　『上の行の書式と数式をコピーして印刷範囲を設定』（こぬこ）　ページの最後に飛ぶ

『上の行の書式と数式をコピーして印刷範囲を設定』（こぬこ）

 Excel2007で表を作ってますが上手くできません…

 「一覧表」シートにA列からP列の24行目まで印刷範囲が設定されています。
 A列は数値セルで、B列からL列には数式が入っています。
 「データシート」から日付を指定して一致するデータを「一覧表」シートに転記します。
 「データシート」のA列の数値を「一覧表」シートのA列に入れます。
 「一覧表」シートは3行目まで見出しで、4行目からデータが入ります。

 この時、該当のデータが24行を超えたら最終行にデータを追加し、上の行を書式・数式もコピーして
 印刷範囲も追加したデータのところまで設定したいのですが、どうしても上の行の書式・数式が
 コピーできず、印刷範囲も設定できません…

 Dim sh2 As Worksheet
 Dim cnt As Long
 Dim x As Long

 Set sh2 = ThisWorkbook.Worksheets("一覧表")

 'cnt でデータの個数をカウントしてます
 With Sheets("データシート")

 If cnt > 21 Then

     x = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
     sh2.Cells(x, 1).EntireRow.Insert copyorigin:=xlFormatFromLeftOrAbove
     sh2.Cells(x, 1).Value = .Cells(i, 1).Value
     sh2.PageSetup.PrintArea = sh2.Range("A1:P" & x)

 End If

 End With

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1057970785

 上記のURLの方法も試しましたが結果は変わりませんでした。

 他のブックで、書式だけをコピーして挿入の時は
 .EntireRow.Insert copyorigin:=xlFormatFromLeftOrAbove
 でできたのですが、書式さえもコピーできません…

 どうしたらよいかお教えください

 いったんレスしたけど、完全に質問の意図を誤解していたので削除。
 (最近、はやとちりの程度が、どんどん激しくなる）

 (ぶらっと)

【1】
＞ 'cnt でデータの個数をカウントしてます
とありますが、肝心のcntに数値代入するステートメントが見当たりません。

したがって、
＞cnt > 21
は必ずFalseになり、Endifまでのステートメントは実行されません。

【2】
前項が解消されても、
＞.Cells(i, 1).Value
の　i　という未宣言で値の代入もされていない変数が使用されています。
このままでは実行時エラーになります。

それぞれの変数の関連性が良く分からないので、
ちょっと具体的なコードにも組みなおしにくい。

私の場合、数式や書式設定を自動拡張、っていうのはもどかしいので、
最初から必要と思われる分を作っておいて、
必要な部分だけ表示した状態を「ユーザー設定のビュー」で登録、
データ数によって「ユーザー設定のビュー」を切り替えるという
方法を取ります。

（みやほりん）

 すみません、全部説明するとややこしくなるので抜粋したのですが、本来は

 ・ユーザーフォームのTextBox1に入れた日付が「データシート」のJ列と同じものを抽出
 ・一覧表には「一覧表一覧表ver.2」「一覧表」「一覧表（イレギュラー）」の3種類がある
 ・それぞれの「N2」セルにTextBox1に入れた日付を入れる
 ・「一覧表一覧表ver.2」「一覧表」は、入っている数式やレイアウトは同じで、行数が1行違う
 （数式は「データシート」からデータを参照するVLOOKUPやSUM関数などが入っています）
 ・「一覧表一覧表ver.2」のデータ個数は20個まで（4～23行目まで）記載可能
 ・データ個数が21個を超えたら「一覧表」にその続きを記載
 ・「一覧表（イレギュラー）」は元から白紙（計算式も入っていない）
 ・データ個数が「一覧表ver2」の範囲内（抽出データが20個まで）だったら「一覧表ver2」と「一覧表（イレギュラー）」を印刷
 ・データ個数が21個以上の場合、「一覧表一覧表ver.2」「一覧表」「一覧表（イレギュラー）」の3つのシートを全て印刷

 なので、現在はデータが41個までは普通に印刷できます。
 滅多にこれを超えることは無いのですが、もしそれを超えるデータがあった場合に、「一覧表」の最終行に
 上の書式と数式をコピーし、印刷範囲を追加したデータまで設定したものを印刷するようにしようとしています

 現在のコードは

 Private Sub 一覧表作成()

 Dim i As Long
 Dim j As Long
 Dim k As Long
 Dim z As Long
 Dim x As Long
 Dim sh1 As Worksheet
 Dim sh2 As Worksheet
 Dim sh3 As Worksheet
 Dim cnt As Long
 Dim flg As Boolean

 flg = False

 Set sh1 = ThisWorkbook.Worksheets("一覧表ver.2")
 Set sh2 = ThisWorkbook.Worksheets("一覧表")
 Set sh3 = ThisWorkbook.Worksheets("一覧表（イレギュラー）")

 sh1.Range("A4:A23").ClearContents
 sh2.Range("A4:A24").ClearContents

 cnt = 0
 j = 4
 k = 4

 sh1.Range("N2").Value = CDate(TextBox1.Text)
 sh2.Range("N2").Value = CDate(TextBox1.Text)
 sh3.Range("N2").Value = CDate(TextBox1.Text)

 With Sheets("データシート")

    z = .Range("B" & .Rows.Count).End(xlUp).Row

    For i = 4 To z
        If .Cells(i, 10).Value = CDate(TextBox1.Text) Then

        If cnt < 20 Then
            cnt = cnt + 1
            sh1.Cells(j, 1).Value = .Cells(i, 1).Value

            j = j + 1

        ElseIf cnt >= 20 Then
            cnt = cnt + 1
            sh2.Cells(k, 1).Value = .Cells(i, 1).Value

            k = k + 1
            flg = True
        ElseIf cnt >= 41 Then
            cnt = cnt + 1
            x = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            sh2.Cells(x, 1).EntireRow.Insert copyorigin:=xlFormatFromLeftOrAbove
            sh2.Cells(x, 1).Value = .Cells(i, 1).Value
            sh2.PageSetup.PrintArea = sh2.Range("A1:P" & x)
            flg = True
        End If

        End If
    Next i

  End With

    If MsgBox("一覧表を印刷しますか？", vbYesNo, "実行確認") = vbYes Then
        If flg = True Then
            sh1.PrintOut
            sh2.PrintOut
            sh3.PrintOut
        ElseIf flg = False Then
            sh1.PrintOut
            sh3.PrintOut
        End If
    End If

 End Sub

 このようになっています。

 これで、データ個数が41個を超えた場合に、「一覧表」のA列に「データシート」のA列の値は入るのですが、
 書式のコピーや印刷範囲の設定ができません。
 「データシート」のA列は ROW関数が入っていて連番になっていて、その値だけを一覧表に貼りつけています。

 最初から必要と思われる個数が日によって全然違うので（データ個数が3件程度の日から40件ぐらいの日もある）
 行を追加しようとしています。
 今までは「一覧表」に「コピーした行を挿入」して印刷範囲も手動で設定していました。
 これをマクロでできないかと思っています。

 ★追記

[[20070227164826]]

 上記の質問の内容を色々試したのですがどうしても書式・数式のコピー、印刷範囲の設定ができませんでした

 （こぬこ）

コードのウワツラは、理屈としては間違っていないけれども、条件判断の手順が問題です。

プログラムは必ず上から下へ処理を行っています。
条件Aが条件Bを内包し、かつ、条件Aの条件判断を先に行うと、
条件Aの処理が優先されます。

ElseIf cnt >= 20 Then
この条件判断は　
ElseIf cnt >= 41 Then　の条件判断を内包してしまっています。
したがって、変数cntが４１以上のときも　２０以上の場合の処理が行なわれていると推測します。

つまり、「cntが４１以上の処理がこの後に書かれているから２０以上の
処理はしなくてもいいか」という風に気を利かせてはくれません。
こいつらは「論理的だが、融通は利かない（I.アシモフ）」。

そのため、
＞A列の値は入るのですが、
＞書式のコピーや印刷範囲の設定ができません。
となります。

cntが４１以上のとき　ElseIf cnt >= 41 Then　以降の処理が実行されない現象は、
ブレークポイントの設定とF8キーによるステップ実行によるデバッグにより
確認することが出来ます。
デバッグの習慣をつけるようにしてください。
メインのスキルも必要ですが、検証スキルはそれ以上に重要です。
つまり、自分のミスに早く気がつき、素直に修正できる人ほど何でも早く上達します。

素振りを何百回しても素振りが正しいフォームで行われているか検証されなければ
無駄な体力と時間を浪費しかねないということと同じです。

解決するには　ElseIf cnt >= 41 Then　の条件判断を先に行うようにしてください。

追記
もしくは、
ElseIf cnt >= 20 Then　を場所はそのまま　ElseIf cnt < 41 Then　とするか。

（みやほりん）

コメント返信：

[ 一覧(最新更新順) ]

[[20120726120334]] 『上の行の書式と数式をコピーして印刷範囲を設定』（こぬこ） ページの最後に飛ぶ

[[20120726120334]]　『上の行の書式と数式をコピーして印刷範囲を設定』（こぬこ）　ページの最後に飛ぶ