[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件分岐で雛形をコピー』（きゃとら）
「入力」というシートに下記のようなデータがあります
A B C D E F G H I J K L 1 倉庫A 倉庫B 合計 2 記号 品名 数量 記号 品名 数量 記号 品名 数量 3 Ap1 りんご 150 Ap2 りんご 300 Ap りんご 450 4 Or1 みかん Or2 みかん Or みかん 0 5 Gp1 ぶどう 2500 Gp2 ぶどう 400 Gp ぶどう 2900 6 Sb1 いちご Sb2 いちご 100 Sb いちご 100
L列には = C3+G3 のような数式が入っています。 このデータを元に、別シートに帳票を出力します。 「入力」シートのデータは全部で50行ほどで、3つの段落があって間に空白行や小計行などがあります。 （A〜G列まではどの行にどのデータが入るかは決まっています）
帳票出力の前に、J〜L列について、上記の「みかん」の行のようにL列が0になる場合は J〜L列を空白にしてほしいとのことだったので、J〜L列には下記のような関数を入力しました
J =IF(L3<>"",LEFT(A3,2),"") K =IF(L3<>"",B3,"") L =IF(C3+G3>0,C3+G3,"")
その上で帳票を出力するのですが、条件がいくつかあります。
１．「倉庫A」と「倉庫B」の数量で、両方ともに数字が入っている場合は帳票の上の欄に倉庫Aのデータ、 下の欄に倉庫Bのデータが入った（1枚で両方の倉庫のデータが入った）帳票を使う ２．「倉庫A」・「倉庫B」どちらか片方しか数量が入っていない場合は数量が入っている倉庫の データのみを記載した帳票を使う ３．どちらかの倉庫の数量が2000を超えた場合は、2000以上用のフォーマットの帳票を使い、 もう片方は上の２のように片方のみの帳票を使う （もう片方が0の場合はその分は無し、もう片方も2000を超えた場合はそれも2000以上用の帳票を使う）
このような条件があるので、帳票の雛形を３つ用意しました。
雛形１は上記の１の条件にあてはまるもの（上のデータで言うと「りんご」のデータ）の場合に使う 雛形２は上記の２の条件にあてはまるもの（「いちご」のデータ）の場合に使う 雛形３は上記の３の条件にあてはまるもの（「ぶどう」のデータ）の場合に使う
帳票のフォーマットは下記のようなものです（雛形１の場合）
A B C D E F G H I J K L M N O P 〜 1 箱詰作業記録 : 4 記号 Ap 品名 りんご 5 6 7 記号 Ap1 数量 150 : : 23 記号 Ap2 数量 300 :
雛形２と雛形３は23行目以降が真っ白だったり手入力するデータ行の部分の行数が違ったりします。 （今回マクロで入力する必要があるのは「記号」「品名」「数量」だけなので雛形２・３では8行目以降の部分は扱いません）
この条件で雛形を選択し、そこにデータを入力するマクロを書いていたのですが、分からない部分がありますのでお教えください。
下記のコードは書きかけなのですが…
Sub リスト作成()
Dim kigo As String '合計の記号 Dim kigo1 As String '一つ目の倉庫の記号 Dim kigo2 As String '二つ目の倉庫の記号 Dim hinmei As String '合計の品名 Dim hin1 As String '一つ目の倉庫の品名 Dim hin2 As String '二つ目の倉庫の品名 Dim qtyAll As Long '合計の数量 Dim qty1 As Long '一つ目の倉庫の数量 Dim qty2 As Long '二つ目の倉庫の数量 Dim i As Long 'カウンタ変数
'数量チェック
With Sheets("入力")
For i = 3 To 17
If .Cells(i, 12) > 0 Then '合計数が0でなかったら
kigo = .Cells(i, 10).Value
hinmei = .Cells(i, 11).Value
qtyAll = .Cells(i, 12).Value
If .Cells(i, 3) = "" Or .Cells(i, 7) = "" Then '倉庫のどちらか片方の数量が空白だったら
If .Cells(i, 7) = "" Then '倉庫Bが空白だったら倉庫Aのデータを変数に格納
kigo1 = .Cells(i, 1).Value
hin1 = .Cells(i, 2).Value
qty1 = .Cells(i, 3).Value
Else '倉庫Aが空白だったら倉庫Bのデータを変数に格納
kigo1 = .Cells(i, 5).Value
hin1 = .Cells(i, 6).Value
qty1 = .Cells(i, 7).Value
End If
ThisWorkbook.Sheets("雛型_2").Copy After:=Sheets(Sheets.Count) '雛形2をコピー
ActiveSheet.Name = kigo & "【" & hinmei & "】" 'シート名を Ap【りんご】 のように変更
ここまで書いて分からなくなったのですが、コピーしたシートにデータを転記する時、コピーしたシートを
どのようにして指定したらいいのでしょう。
普通に指定する時は
With Sheets("入力")
のようにシート名を指定したり、シートを変数に格納したりすると思うのですが、この場合のコピー先シートの
指定の仕方をお教えください。
※データ行ごとにシートをコピーしますので、該当データが20件あった場合は20枚のシートがコピーして作成されることになります。
また、上記の書きかけのコードは「どちらかの数量が2000以上だった場合」を考慮していないままなのですが、 このコードの If .Cells(i, 3) = "" Or .Cells(i, 7) = "" Then の上に If .Cells(i, 3) >=2000 Or .Cells(i, 7) >=2000 Then このように条件文を入れて分岐させるべきでしょうか。 それともこのように If 分岐をいくつも入れるよりもスマートなやり方があるのでしょうか。 （マクロは簡単なものしか作ったことがないので…） Select文というのはそのような構文があるのは知っていますが使ったことがなくて、こちらの方が 多数の条件に適しているのかどうかわからなくて…
分かりにくくてすみません… 不足部分は補いますのでよろしくお願いします。
Excel2007でWindows7です。
＞帳票を出力するのですが と言う事なので、印刷するだけですか？
それとも、 ＞手入力するデータ行の部分 が有ったり ＞※データ行ごとにシートをコピーしますので、 ＞該当データが20件あった場合は20枚のシートがコピーして作成されることになります。 と書いてあるので、それぞれをシートとして残しておく必要が有るのですか？
最大で20件程度なのでしょうか？ ３番の様なデータが有る事を考えると 20件あったら 可能性としては最大40枚でしょうけど？
＞「入力」シートのデータは全部で50行ほどで、3つの段落があって間に空白行や小計行などがあります その行はどの様に成っているのですか？ （サンプルデータには無い様ですが。。。？）
＞（A〜G列まではどの行にどのデータが入るかは決まっています） 決まっているのは、行ではなく、列 ですよね？
印刷するだけなら 雛形１、雛形２−Ａ、雛形２−Ｂ、雛形３−Ａ、雛形３−Ｂ を用意しておいて、それぞれのセルには数式でリンク。 マクロで ・何行目のデータを表示するか ・どの雛形を印刷するのか だけを制御する事にすると、マクロの方のウエイトは 軽くて済むのではないかと思いますが。
(HANA)
自力で何とかコードを書いたので（2000件以上のものにはまだ対応していませんが）質問を編集しようをすると HANA様からご回答をいただいてました。
>印刷するだけですか？ >それぞれをシートとして残しておく必要が有るのですか？ 印刷するだけですが、一旦全てのシートを作成し、保存してから印刷します。
> ３番の様なデータが有る事を考えると >20件あったら 可能性としては最大40枚でしょうけど？ すみません、その通りです…
>その行はどの様に成っているのですか？ 例えば3〜17行目までは果物のデータ、 18行目は果物の件数の小計（SUM関数） 19行目空白、20行目に野菜データの見出し、21〜31行は野菜のデータ、32行は野菜の小計 33行目空白、34行目にその他のデータの見出し、35〜45行はその他のデータ、46行はその他の小計 のように分かれています。 列ごとのレイアウトは同じです。
>決まっているのは、行ではなく、列 ですよね？ 列も決まっていますが、行も決まっています。（例えば3行目はいつもりんごです）
それで、見よう見真似でマクロを書きました
Option Explicit
Sub リスト作成()
Dim kigo As String '合計の記号 Dim kigo1 As String '一つ目の倉庫の記号 Dim kigo2 As String '二つ目の倉庫の記号 Dim hinmei As String '合計の品名 Dim hin1 As String '一つ目の倉庫の品名 Dim hin2 As String '二つ目の倉庫の品名 Dim qty1 As Long '一つ目の倉庫の数量 Dim qty2 As Long '二つ目の倉庫の数量 Dim lsh As Worksheet Dim i As Long Dim j As Long Dim k As Long
With Sheets("入力")
For i = 3 To 17
If .Cells(i, 12) <> "" Then
kigo = .Cells(i, 10).Value
hinmei = .Cells(i, 11).Value
If .Cells(i, 3) = "" Or .Cells(i, 7) = "" Then
If .Cells(i, 7) = "" Then
kigo1 = .Cells(i, 1).Value
hi1 = .Cells(i, 2).Value
qty1 = .Cells(i, 3).Value
Else
kigo1 = .Cells(i, 5).Value
hi1 = .Cells(i, 6).Value
qty1 = .Cells(i, 7).Value
End If
ThisWorkbook.Sheets("雛型_2").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = kigo & "【" & hinmei & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4") = kigo
lsh.Range("P4") = hinmei
lsh.Range("C7") = kigo1
lsh.Range("K7") = qty1
Else
kigo1 = .Cells(i, 1).Value
hi1 = .Cells(i, 2).Value
qty1 = .Cells(i, 3).Value
Ten2 = .Cells(i, 5).Value
hi2 = .Cells(i, 6).Value
qty2 = .Cells(i, 7).Value
ThisWorkbook.Sheets("雛型_1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = kigo & "【" & hinmei & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4") = kigo
lsh.Range("P4") = hinmei
lsh.Range("C7") = kigo1
lsh.Range("K7") = qty1
lsh.Range("C23") = Ten2
lsh.Range("K23") = qty2
End If
End If
Next i
For j = 21 To 31
If .Cells(j, 12) <> "" Then
kigo = .Cells(j, 10).Value
hinmei = .Cells(j, 11).Value
If .Cells(j, 3) = "" Or .Cells(j, 7) = "" Then '
If .Cells(j, 7) = "" Then
kigo1 = .Cells(j, 1).Value
hi1 = .Cells(j, 2).Value
qty1 = .Cells(j, 3).Value
Else
kigo1 = .Cells(j, 5).Value
hi1 = .Cells(j, 6).Value
qty1 = .Cells(j, 7).Value
End If
ThisWorkbook.Sheets("雛型_2").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = kigo & "【" & hinmei & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4") = kigo
lsh.Range("P4") = hinmei
lsh.Range("C7") = kigo1
lsh.Range("K7") = qty1
Else
kigo1 = .Cells(j, 1).Value
hi1 = .Cells(j, 2).Value
qty1 = .Cells(j, 3).Value
Ten2 = .Cells(j, 5).Value
hi2 = .Cells(j, 6).Value
qty2 = .Cells(j, 7).Value
ThisWorkbook.Sheets("雛型_1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = kigo & "【" & hinmei & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4") = kigo
lsh.Range("P4") = hinmei
lsh.Range("C7") = kigo1
lsh.Range("K7") = qty1
lsh.Range("C23") = Ten2
lsh.Range("K23") = qty2
End If
End If
Next j
For k = 35 To 45
If .Cells(k, 12) <> "" Then
kigo = .Cells(k, 10).Value
hinmei = .Cells(k, 11).Value
If .Cells(k, 3) = "" Or .Cells(k, 7) = "" Then '
If .Cells(k, 7) = "" Then
kigo1 = .Cells(k, 1).Value
hi1 = .Cells(k, 2).Value
qty1 = .Cells(k, 3).Value
Else
kigo = .Cells(k, 5).Value
hi1 = .Cells(k, 6).Value
qty1 = .Cells(k, 7).Value
End If
ThisWorkbook.Sheets("雛型_2").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = kigo & "【" & hinmei & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4") = kigo
lsh.Range("P4") = hinmei
lsh.Range("C7") = kigo1
lsh.Range("K7") = qty1
Else
kigo1 = .Cells(k, 1).Value
hi1 = .Cells(k, 2).Value
qty1 = .Cells(k, 3).Value
Ten2 = .Cells(k, 5).Value
hi2 = .Cells(k, 6).Value
qty2 = .Cells(k, 7).Value
ThisWorkbook.Sheets("雛型_1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = kigo & "【" & hinmei & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4") = kigo
lsh.Range("P4") = hinmei
lsh.Range("C7") = kigo1
lsh.Range("K7") = qty1
lsh.Range("C23") = Ten2
lsh.Range("K23") = qty2
End If
End If
Next k
End With
End Sub
慣れないもので不格好なコードですが…
これを、2000件以上に対応するようにしたいと思います。 しかし2000件以上と言っても、
パターン1：倉庫A・Bともに2000件以上だった場合 パターン2：どちらかの倉庫が2000件以上で、もう片方は2000件未満の数字が入っていた場合 パターン3：どちらかの倉庫が2000件以上で、もう片方は0件だった場合
この3つが考えられるので、上のコードに直接条件分岐を入れるより、2000件以上の倉庫があった場合のコードを用意して、 それを呼び出して使うようにと思いました。
その場合、2000件以上のコードの部分は同じように Sub で始めて
Sub リスト作成() : If 2000件以上の倉庫があった場合 Call 2000件以上のプロシージャ :
End Sub
Sub 2000件以上()
:
End Sub
このような形式でよろしいのでしょうか。
一つのプロシージャの終わりに Call で別の処理を実行するプロシージャを入れたことはありますが、 今回のように、行単位で条件を見て行って、途中で条件に一致するもののみ別のプロシージャを使うというのが よくわからなくて…
ちなみに印刷は、ユーザーフォームのリストボックスにシートの一覧を表示して、そこから印刷するものを作りました。
（きゃとら）
答えに成っているかどうか分からないですが、例えば
雛形2をコピーして、データを書き込む部分ですが
ThisWorkbook.Sheets("雛型_2").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = kigo & "【" & hinmei & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4") = kigo
lsh.Range("P4") = hinmei
lsh.Range("C7") = kigo1
lsh.Range("K7") = qty1
これを
Sub 雛型_2コピー(H記号 As String, H品名 As String, D記号1 As String, D数量1 As Long)
Dim lsh As Worksheet
ThisWorkbook.Sheets("雛型_2").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = H記号 & "【" & H品名 & "】"
Set lsh = ThisWorkbook.Sheets(Sheets.Count)
lsh.Range("E4").Value = H記号
lsh.Range("P4").Value = H品名
lsh.Range("C7").Value = D記号1
lsh.Range("K7").Value = D数量1
Set lsh = Nothing
End Sub
こんな感じにして呼び出して使う事にしておけば、とにかく最初に変数に値を入れておいて
kigo = .Cells(i, 10).Value
hinmei = .Cells(i, 11).Value
kigo1 = .Cells(i, 1).Value
hi1 = .Cells(i, 2).Value
qty1 = .Cells(i, 3).Value
kigo2 = .Cells(i, 5).Value
hi2 = .Cells(i, 6).Value
qty2 = .Cells(i, 7).Value
If qty1 = "" Or qty2 = "" Then
If qty2 = "" Then
Call 雛形_2コピー(kigo, hinmei, kigo1, qty1)
Else
Call 雛形_2コピー(kigo, hinmei, kigo2, qty2)
End If
Else
Call 雛形_1コピー(kigo, hinmei, kigo1, qty1, kigo2, qty2)
End If
こんな感じで纏められるのではないかと思います。
If .Cells(i, 7).Value = "" Then の後に、 .Cells(i, 3).Value が2000以上か、2000未満か？ ・・・ 雛形_3コピーか、雛形_2コピーか Else の後にも .Cells(i, 7).Value が2000以上か、2000未満か？・・ 雛形_3コピーか、雛形_2コピーか
If .Cells(i, 3).Value = "" Or .Cells(i, 7).Value = "" Then の Else の後にも 両方が2000以上か、2000未満か？ ・・・ 雛形_3コピーを２回か、雛形_1コピーか
とはめ込んでいけるのではないかと思います。
どの順番で、何を確認していくかは好き好きですが 最初に どちらのセルも0より大で、2000未満の値が入っている・・・・雛形_1コピー をやってしまえば、後は一つずつ確認して 一つずつ処理しても 良いようにも思えます。（自分の値だけを確認すれば良いので）
内容とは少し離れますが
コードを書いている所の先頭に「Option Explicit」を入れておくのが良いと思います。 また、「.Value」が無い所があるので、つけておくのと Set したら Nothing も やる癖を付けておくのが良いと思います。
(HANA)
HANA様 ありがとうございます。 お陰さまですっきりコードが書けました。
>コードを書いている所の先頭に「Option Explicit」を入れておくのが良いと思います >また、「.Value」が無い所があるので、つけておくのと >Set したら Nothing も やる癖を付けておくのが良いと思います。 ありがとうございます。 Set の後に Nothing が必要という認識がありませんでした…
ところで1点お聞きしたいのですが、qty1 や qty2 という数量のところで、「型が一致しません」のエラーが出ます。 宣言部分を Long型ではなく Variant型にしたら出なくなりました。
空白部分に数値が入っていないからでしょうか。 セルの書式設定は「標準」になっています。
（きゃとら）
空白部分に【文字】が入っていませんか？ 例えば、数式で =IF(条件,"",計算式) の様な。
ISTEXT関数等で該当セルの値を調べてみて下さい。
「私はこの変数には値を入れるからね」と言っていたのに、違うことをしようとすると エクセル君は親切なので「これって値じゃないんだけど。なんか勘違いしてない？」 と教えてくれます。
Variantでしたら、「とにかく何か」なので、何でも入ります。
と言う事で。。。。 ＞If qty1 = "" Or qty2 = "" Then この辺りの書き方も 実際はおかしかったですね、済みません。
(HANA)
調べてみましたが、L列以外のC列(qty1)、G列(qty2)には文字や数式は入っていませんでした。
とりあえず今回のマクロは帳票に転記さえできればよいので、Variant型でやってみます。
（きゃとら）
って事は、入れる時（qty1 = .Cells(i, 3).Value）ではなく 比較の所（If qty1 = "" Or qty2 = "" Then）でしたか？
でしたら、済みませんが「0」と比較して下さい。
ローカルウィンドウを表示させると、使用している変数と そのデータを確認する事が出来ます。
マクロの実行時 [ステップイン]を選んで実行してみて下さい。 VBEが立ち上がって、黄色くハイライトされた行が有ると思います。
それから [F8] を押して、一行ずつ実行して下さい。 .Cells(i, 3).Value に何も入力が無かった時 qty1 の値は「0」に成るのが 確認出来ると思います。
その他の部分も「0」に変えてもらったら 変数の型は Long のままで 大丈夫に成ると思います。
(HANA)
HANA様
If qty1 = "" Or qty2 = "" Then を If qty1 = 0 Or qty2 = 0 Then にしたらLong型でいけました。
ありがとうございました！
（きゃとら）
お手数お掛けしました、有り難う御座います。(｀・ω・´)ゞ
ついでに（？）条件分岐案。
If 0 < qty1 And qty1 < 2000 And _
0 < qty2 And qty2 < 2000 Then
Call 雛形_1コピー(kigo, hinmei, kigo1, qty1, kigo2, qty2)
Else
If qty1 >= 2000 Then
Call 雛形_3コピー(kigo, hinmei, kigo1, qty1)
ElseIf qty1 > 0 Then
Call 雛形_2コピー(kigo, hinmei, kigo1, qty1)
End If
If qty2 >= 2000 Then
Call 雛形_3コピー(kigo, hinmei, kigo2, qty2)
ElseIf qty2 > 0 Then
Call 雛形_2コピー(kigo, hinmei, kigo2, qty2)
End If
End If
これで、パターンが網羅出来ていると思います。
あとは、間の行の処理をとばせる様な方法を考えると 同じコードを書く回数が減るので良いと思います。
今は「If .Cells(i, 12) <> "" Then」で分岐していますが、 間の行で12列目に何か入力される事が有るのでしょうか？ 入力されないなら、iは3〜45まで一気に行っても良い様に思います。 また、J:L列はマクロで使う以外に何かに使うのでしょうか？ セルの値をみてデータ行かそうでないのか、見分けられる様な部分は無いでしょうか？
(HANA)
たびたびありがとうございます。
条件分岐案、私が書いたものよりずっとすっきりしてます！！ ↓私が書いたコード
For i = 3 To 17
If .Cells(i, 12) <> "" Then
kigo = .Cells(i, 10).Value
hinmei = .Cells(i, 11).Value
kigo1 = .Cells(i, 1).Value
hin1 = .Cells(i, 2).Value
qty1 = .Cells(i, 3).Value
kigo2 = .Cells(i, 5).Value
hin2 = .Cells(i, 6).Value
qty2 = .Cells(i, 7).Value
If qty1 = 0 Or qty2 = 0 Then
If qty2 = 0 Then
If qty1 >= 2000 Then
Call 雛型_3コピー(kigo, hinmei, kigo1, qty1)
Else
Call 雛型_2コピー(kigo, hinmei, kigo1, qty1)
End If
Else
If qty2 >= 2000 Then
Call 雛型_3コピー(kigo, hinmei, kigo2, qty2)
Else
Call 雛型_2コピー(kigo, hinmei, kigo2, qty2)
End If
End If
Else
If qty1 >= 2000 Or qty2 >= 2000 Then
If qty1 >= 2000 And qty2 < 2000 Then
Call 雛型_3コピー(kigo, hinmei, kigo1, qty1)
Call 雛型_2コピー(kigo, hinmei, kigo2, qty2)
ElseIf qty1 < 2000 And qty2 >= 2000 Then
Call 雛型_3コピー(kigo, hinmei, kigo1, qty1)
Call 雛型_2コピー(kigo, hinmei, kigo2, qty2)
Else
Call 雛型_2コピー(kigo, hinmei, kigo1, qty1)
Call 雛型_2コピー(kigo, hinmei, kigo2, qty2)
End If
Else
Call 雛型_1コピー(kigo, hinmei, kigo1, qty1, kigo2, qty2)
End If
End If
End If
Next i
>間の行で12列目に何か入力される事が有るのでしょうか？ はい。SUM関数でそれぞれの小計が入ってます。 （18・32・46行目）
>J:L列はマクロで使う以外に何かに使うのでしょうか？ この「入力」シート自体を印刷して、チェックリストに使います。 なのでデータが「0」件であればチェック不要なので、見やすいように J =IF(L3<>"",LEFT(A3,2),"") K =IF(L3<>"",B3,"") L =IF(C3+G3>0,C3+G3,"") このような関数で空白行を作るようにしています。
>セルの値をみてデータ行かそうでないのか、見分けられる様な部分は無いでしょうか？ 関数が入っているかどうかでしか見分けられないと思います…
（きゃとら）
＞条件分岐案、私が書いたものよりずっとすっきりしてます！！ 条件分岐は、何をどの順番で分岐して行くかで長さが変わったりしますので 「誰かにやってもらおうと思った時、どう説明しておけば短い説明で済むか」 と考えてもらえると良いと思います。 「だから 要するに どういう事？」って部分です。
そうそう、 ＞If .Cells(i, 12) <> "" Then は、一応他の所に倣って If .Cells(i, 12).Value <> "" Then って書いておくと良いと思います。
＞関数が入っているかどうかでしか見分けられないと思います… そうですか？ 色々有りそうだと思いますけどねぇ。。。
A列やE列はどうですか？ 小計が入っている行はどこかのセルに「小計」の文字等入って居ませんか？
18,19,20行目がどうなっているか 詳しく教えてもらえませんか？
18行目が小計で、19行目が空行で、20行目が次の表の見出し ですかね。。。？ 18行目が小計で、19,20行目が次の表の見出し かな。。。？
(HANA)
> A列やE列はどうですか？ >小計が入っている行はどこかのセルに「小計」の文字等入って居ませんか？ 「小計」などの文字は入ってないです。 18〜20行目は下記のようになっています。
A B C D E F G H I J K L 18 SUM関数 19 作業簿_野菜 20 記号 品名 数量 記号 品名 数量 記号 品名 数量
このようになっています。32〜34行目も同じです。
なので「L列の"数量"という見出しの1行下から、次の"数量"の文字の3行前まで」が一つの段落ですが、 最後の「その他」は「次の"数量"」の文字が無いので、ここは「45行目まで」と指定するしかないのかな… （L46には「その他」の小計のSUM関数が入っています）
（きゃとら）
K列の数式が ＞K =IF(L3<>"",B3,"") なので、データ範囲（3〜17行）は処理対象外の行が「""」に成っています。
18,19行目も K列には入力が無い様です。
後は20行目だけが If .Cells(i, 11).Value <> "" Then で分岐出来ないですが、ここは個別に If .Cells(i, 11).Value <> "" Or .Cells(i, 11).Value <> "品名" Then とかで良いんじゃないです？
そしたら、
Sub リスト作成()
〜 変数の宣言 〜
With Sheets("入力")
For i = 3 To 45
〜 K列で判定→コピー処理 〜
Next i
End With
End Sub
の一ブロックで良くなりそうですが。
(HANA)
ありがとうございます。 おっしゃるようにコードを書き換えてみたらちゃんと作成されました。 この方が間の行数に変更があった場合に対応しやすくてよいですね。 助かりました！
（きゃとら）
上手く行きましたか 良かったです。
＞この方が間の行数に変更があった場合に対応しやすくてよいですね。 そうですね。最終行の数字を一箇所変えるだけで良いので。
あとは、この数字自体も自動で取得する方法も考えられますが。。。 ご希望が有れば。
さっそくですが、先ほど K列で見れば良いんじゃない？ とサンプルを書きましたが、よく考えると 「なんで K列？ちゅ〜とはんぱ！！」 ですよね。。。
今のところ 元々L列で見ていて、L列だと都合が悪いのでその一つ前の列の・・・ で、K列に成っているのですが、J〜Lが一つのまとまりで 「J列だと問題が有ってK列の方が都合がよい」なんて積極的な理由も無いですし それなら、まとまりの先頭のJ列で見ておいた方が すっきりしますよね。 If .Cells(i, 10).Value <> "" Or .Cells(i, 10).Value <> "記号" Then
・・・と、昼食を食べながら思いました。
(HANA)
>あとは、この数字自体も自動で取得する方法も考えられますが。。。 そうなんですか！！
もしよろしければご教授くださいm(__)m
おっしゃるようにJ列でも問題ないですよね。 ここも変更してみます。
（きゃとら）
今は、L46が一番下の行ですね？
でしたら、L50セルを選択して Ctrl + ↑ をしてみて下さい。 アクティブセルが L46 に移動したと思います。 処理はこのセルの行まで実行されれば良いです。 最終行を確認する列がL列からずれた場合は問題ですが。。。 少なくともL列は人の手でデータの最終行まで数式が入るとは思いますので。
今は L46が一番下のセルだと分かっているので、L50から上方向に移動しましたが 今後は何処が一番下のセルになるか分からないです。 もしも L60とかが一番下なのに L50から上方向に探したのでは見つからないです。
そこで、ワークシートの一番下の行のセルから 上方向へ探します。
ワークシートの行数が Rows.Count で取得出来るので
L列の一番下の行のセル が
.Cells(Rows.Count, 10)
Ctrl + ↑ が
.End(xlUp)
そのセルの行番号が
.Row
なので
For i = 3 To .Cells(Rows.Count, 10).End(xlUp).Row
L列だと小計行までループする事に成るので（処理はされませんが）
A列で見るのが素直なのかもしれないです（？）
For i = 3 To .Cells(Rows.Count, 1).End(xlUp).Row
これは実際の状況を考えて どの列で確認するのが良いか決めてもらえると良いと思います。 なかなか将来を見通すのも大変ですが。
(HANA)
HANA様
重ね重ねありがとうございます。
A列で見て行っても問題なさそうです。 これだと最終行が変わっても大丈夫なので、後々のことを考えるとこの方が良さそうです。 ありがとうございました！
（きゃとら）
HANA様
何度もすみません…
If .Cells(i, 10).Value <> "" Or .Cells(i, 10).Value <> "記号" Then
これでやると、20行目に「記号」の見出しが入っているところで何故かシートをコピーしようとして「型が一致しません」 （見出し行の「数量」欄に数値が入っていないため）のエラーが出ます。
If .Cells(i, 10).Value <> 0 Or .Cells(i, 10).Value <> "記号" Then
これで試しても同じでした。
何故 .Cells(i, 10).Value <> "記号" の条件が無視されるのでしょう…
（きゃとら）
その時の i の値が分かりますか？ （って言っても、二ヶ所しか無いので両方確認しても大した事は無いと思いますが）
簡単に思いつくところだと、 セルに入力されている「記号」の方は、後にスペースなどが入っていて 実際は「記号 」の様に成っている。 すると "記号" と比べるので一致しないです。 LEN関数などで調べてみるとどうですか？
K列の品名 ＞If .Cells(i, 11).Value <> "" Or .Cells(i, 11).Value <> "品名" Then では上手く行ったのですよね？
(HANA)
>If .Cells(i, 10).Value <> 0 Or .Cells(i, 10).Value <> "記号" Then .Cells(i, 10).Value が 0でない か "記号"でない なら ・・・
.Cells(i, 10).Value が 0の場合 "記号"でない → 実行される。 .Cells(i, 10).Value が "記号"の場合 0でない → 実行される。 0でも"記号"でもない場合 "記号"でなく0でない → 実行される。
結局どの場合でも実行されます。 （あん）
i の値は 20 です。 なので2段落目（野菜）の見出し行でつまづいているようです。
私も空白などが含まれているかと思い、セルの値を直接コピーしたりしましたがダメでした。
苦肉の策で
If .Cells(i, 12).Value <> "" And IsNumeric(.Range("L" & i).Value) = True Then
にしてみたら動きましたが、これで問題ないのだろうかと…
>K列の品名 >＞If .Cells(i, 11).Value <> "" Or .Cells(i, 11).Value <> "品名" Then >では上手く行ったのですよね？ 確か上手く行ったはずなんですが、今その部分を戻して試すとやはり同じエラーになります。 もしかしたらその時はまだブロックを分けたまま（For i = 3 To 45 にする前）だったかもしれません。。。
ここまで書いて衝突しました。
あん様
あーっ！！
さんざん考えたはずなのに初歩的ミス…orz
Or を And に変えました…お騒がせして申し訳ございません…
（きゃとら）
私も衝突☆
あっっ、本当ですね。。。
たびたび済みません。
.Cells(i, 10).Value が 0でない 且つ "記号"でない
~~~~＼で指定しないといけないですよね。
And で繋げて下さい。
あんさん、ご指摘有り難う御座います。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.