【Excel VBA】抽出したデータを書式へ

質問

Excel2003を使用しています。

Excelで作成→送付されてくる全店データの中から自店のデータのみを抽出し、全店データと同じ書式で自店のみのデータを作成したく、下記のようにコードを書きました。

-------------------------------------------------------
Sub Macro1()
Dim i As Long
Dim j As Long
Dim LastR As Long

LastR = Sheets("全店データ").Range("E65536").End(xlUp).Row

With Sheets("自店データ")
　For i = 34 To LastR
　　If Sheets("全店データ").Cells(i, "E").Value Like "*A店*" Then
　　　.Cells(j, "C").Value = Sheets("全店データ").Cells(i, "C").Value
　　　.Cells(j, "E").Value = Sheets("全店データ").Cells(i, "E").Value
　　　.Cells(j, "I").Value = Sheets("全店データ").Cells(i, "I").Value
　　　.Cells(j, "W").Value = Sheets("全店データ").Cells(i, "W").Value
　　　.Cells(j, "AA").Value = Sheets("全店データ").Cells(i, "AA").Value
　　　.Cells(j, "AE").Value = Sheets("全店データ").Cells(i, "AE").Value
　　　.Cells(j, "AI").Value = Sheets("全店データ").Cells(i, "AI").Value
　　　.Cells(j + 1, "C").Value = Sheets("全店データ").Cells(i + 1, "C").Value
　　　.Cells(j + 1, "I").Value = Sheets("全店データ").Cells(i + 1, "I").Value
　　　j = j + 2
　　End If
　Next i
End With
End Sub
-------------------------------------------------------

元の書式は、データ1件が2行で、結合セルも含まれているので、コピペするより、Value = Value が扱いやすい(？)かと思い、上記のように書いてみました。

自店のデータが1ページ分(14件分28行で、セル34行目～61行目)内に収まる場合は問題ないのですが、それ以上になった場合をどのように記述すればよいのか躓いています。
書式は、
　1ページ目　34行目～61行目
　2ページ目　73行目～128行目
　3ページ目　140行目～195行目
　4ページ目　207行目～262行目
　5ページ目　274行目～329行目
　6ページ目　341行目～396行目

このような書式にデータを代入する場合は、上記の方法では難しいでしょうか？
説明が分かりづらくて申し訳ありませんが、よろしくお願いします。

回答No.3

fujillin · ベストアンサー

ｊの値の初期値（=34？）が設定されていないようですが、ｊの値がご提示のように行数に応じて変化してくれればいいのですよね？

現状のものを修正するのであれば、少々わかりづらいかも知れませんが…

j=j+2として次に記入すべき行を求めていますが、この時に、ページの終わりだったらj=j+13としてあげればよいと思われます。
If (j - 60) Mod 67 = 0 Then j = j + 13 Else j = j + 2
（もしかすると、全体がずれているかも知れませんが…）

別の考え方として、
記入している件数をカウントする変数（例えばcnt）を作成しておきます。
これに対して、記入すべき行数が求められる関数を作成しておいて、その関数が示す行に記入するという考え方もできます。
（このほうが、考えやすいと思います）
関数をg()とするなら、
g(1)=34, g(14)=60, g(15)=73, g(42)=127, g(43)=140　・・・・となるようなもの。

今回の場合は規則性があるので、g()は外部関数にするほどでもなく
　Int((cnt - 15) / 28) * 67 + ((cnt - 15) Mod 28) * 2 + 73
の1行で表すことができますが、こうしておけば、もっと複雑な場合や、単に数式だけでは表せない場合などでも対応可能ですし、そのような場合は別関数にしてしまえば扱いやすくなると思います。

お礼

アドバイスありがとうございます。

> ｊの値の初期値（=34？）が設定されていないようですが

あーでもないこーでもないとコードを書き換えながら試した後、質問文にコードをコピペしましたので、jの初期値(=34)を記述するのを忘れていました…。

今回の場合は規則性があるので、Ifで条件を追加すれば、うまく動作しそうなのに、その条件をどのように表示してよいかわからず、質問させていただきましたが、丁寧に説明してくださったおかげで理解することができ、無事、解決に至りました。
ありがとうございました。

回答No.5

_Kyle

#3さまと基本的に同趣旨の内容ですが…。
-------------------------------------
　自店１件目　⇒　34～35行目
　自店18件目　⇒　79～80行目
　自店63件目　⇒　180～181行目
に転記したい場合、

例えば、下記のように
【自店○件目のデータを■行目に転記すればよいか】
を計算で求めてやれば、ご要望のように動作するはずです。

'==========================↓　ココカラ　↓==========================
Sub Macro1()
　Dim i　　 As Long
　Dim j　　 As Long
　Dim k　　 As Long
　Dim LastR As Long
　Dim alSht As Worksheet
　Set alSht = Sheets("全店データ")

　LastR = alSht.Range("E65536").End(xlUp).Row
　j = 1
　With Sheets("自店データ")
　　For i = 34 To LastR
　　　If alSht.Cells(i, "E").Value Like "*A店*" Then
　　　　k = 6 + Int((j + 13) / 28) * 67 + ((j + 13) Mod 28) * 2
　　　　.Cells(k, "C").Value = alSht.Cells(i, "C").Value
　　　　.Cells(k, "E").Value = alSht.Cells(i, "E").Value
　　　　.Cells(k, "I").Value = alSht.Cells(i, "I").Value
　　　　.Cells(k, "W").Value = alSht.Cells(i, "W").Value
　　　　.Cells(k, "AA").Value = alSht.Cells(i, "AA").Value
　　　　.Cells(k, "AE").Value = alSht.Cells(i, "AE").Value
　　　　.Cells(k, "AI").Value = alSht.Cells(i, "AI").Value
　　　　.Cells(k + 1, "C").Value = alSht.Cells(i + 1, "C").Value
　　　　.Cells(k + 1, "I").Value = alSht.Cells(i + 1, "I").Value
　　　　j = j + 1
　　　End If
　　Next i
　End With
End Sub
'==========================↑　ココマデ　↑==========================

なお、余談ですが、
VBAのMOD演算子と、ワークシート関数のMOD関数では、
被除数が負数のときの結果が異なるので注意が必要です。

以上ご参考まで。

お礼

アドバイスありがとうございます。

記載していただいたコードで試してみたところ、希望通り動作しました。
順を追って詳しく説明していただいたおかげで、わかりやすくて助かりました。

> 自店○件目のデータを■行目に転記すればよいか

↑をうまく計算で求めることができませんでした。
VBAのMOD演算子は今回初めて使用しましたが、大変勉強になりました。
ありがとうございました。

回答No.4

imogasi

エクセルでは「書式」は特別な意味が有るから、この質問には使わないで、様式やフォーマットや項目の配置などと表現すべきと思います。実際は行も列も多いのでしょうが、コードを書くより、少数列、行の模擬実例にして、質問に書いてください。
後自分の場合に引きなおすのは簡単なはずです。あなたの表の様子が読者には見えません。
読者でコードから逆解析せよ、質問文でわかる人だけ答えろと言うのは、質問者として、配慮が足りない。
ここの質問コーナーは個人の要求を満たす一方、公器でもあるとおもう。
ーーー
j = j + 2
は全店分で元の表が２行ずつなのか？そういうことも例を見ないと判りにくい。

お礼

アドバイスありがとうございます。

> 少数列、行の模擬実例にして

表として記載することが難しかったため、コードを記載させていただきましたが、幸い今回は他の方からいただいた回答で無事解決いたしました。

直接の回答ではない細かいところまでアドバイスをいただき、お手数をおかけしました。

回答No.2

Sinogi

>書式のページ間は不要データではなく、小計や合計を表示する行などが含まれているので、全て削除することはできません。

列データに影響があるということかな?
作業シートでの処理ですから、
1.元シートを全セルコピーし、作業シートに値貼り付け
2.ページ間の行を削除

を提示コードの当初に実行し、Sheets("全店データ").をSheets("作業シート").に変更するということです。

お礼

再度のアドバイスありがとうございます。

データ転記先の「自店データ」シートも元シートの「全店データ」シートと同じ書式のため、転記するデータが1ページ分以上になった場合に、例えば2ページ目の1件目のデータを転記するセルをどのように指定したらよいのかがわからず、質問させていただきました。

今回は他の方からいただいた回答で解決できました。
何度もお手数をおかけして申し訳ありません。ありがとうございました。

回答No.1

Sinogi

私なら作業シートを作成しページ間の不要データを削除します。

お礼

アドバイスありがとうございます。
説明不足で申し訳なかったのですが、書式のページ間は不要データではなく、小計や合計を表示する行などが含まれているので、全て削除することはできません。