🎄teratailクリスマスプレゼントキャンペーン2024🎄』開催中!

\teratail特別グッズやAmazonギフトカード最大2,000円分が当たる!/

詳細はこちら
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

1回答

3739閲覧

[Excel/VBA]オートフィルタ適用中のfor if 文 処理方法

doggyman10

総合スコア5

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2021/03/11 04:03

オートフィルタで抽出された内容(可視セル)のみでfor if next 文の処理を行いたいものの、
上手くいかないためご質問させていただきました。

【使用データ】
1.BOOK0(マクロ)
2.BOOK1(転記先データ)
3.BOOK2(検索データ)

【処理内容】
①BOOK2を条件下でオートフィルタの適用
条件内容:日付(yyyy/mm/ddまで)と空白
②BOOK2オートフィルタ適用中のまま、BOOK1 C列とBOOK2C列で一致するものをif文で判別
③②でヒットした場合、BOOK2 E列に入力されている日付をBOOK1 E列に転記
という流れにしたいと考えております。
現状動くのは動くのですが、オートフィルタの条件(日付と空白)が反映されないまま転記されています。
ちなみにオートフィルタの条件は問題ないことと正常であることは確認できております。

.SpecialCells(xlCellTypeVisible)を用いて可視セルのみを対象となるように試してみたのですが、
差込どころが悪いのかうまく作動しませんでした。

以下対象となるコード(一部)を記載いたしますので、
有識者のみなさまのお力をお借りさせて頂きたく存じます。
※VBA 勉強中のためコードに不備点や見づらい点もあるかと思いますがご了承頂けますと幸いです。

'BOOK1C列とBOOK2C列 一致→D列にBOOK2B列に入力されている日付をBOOK1B列へ転記 Dim i As Long Dim j As Long 'フィルター設定 wb2.Sheets("sheet1").Range("A1").AutoFilter Field:=5, Criteria1:="<=" & date(日付) wb2.Sheets("sheeet1").Range("A1").AutoFilter Field:=14, Criteria1:="" For j = 3 To wb2.Sheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Row For i = 4 To wb1.Sheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Row If wb1.Sheets("sheet1").Cells(i, "C") = wb2.Sheets("sheet1").Cells(j, "C") Then wb1.Sheets("sheet1").Cells(i, "E") = Format(wb2.Sheets("sheet1").Cells(j, "E"), "yyyy/mm/dd") End If Next i Next j wb2.Sheets("sheet1").Range("A1").AutoFilter End Sub

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

たぶんこんな感じ?

VBA

1If Not wb2.Sheets("sheeet1").Rows(j).Hidden Then

投稿2021/03/11 04:31

jinoji

総合スコア4592

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

doggyman10

2021/03/11 06:30

Jinoji様 早速のご回答頂きありがとうございます。 ネット上で検索した時もそれらしきものがあり試したのですが その時For文の制御構造が異なるのかうまく作動できておりませんでした。 ネット上で検索した時と似たようなコードが回答に記載されるということは 恐らく間違っていないだろうと再度制御構造や組み合わせの問題を確認しましたところうまく作動しました。 勉強になりました。本当にありがとうございます。
jinoji

2021/03/11 06:38

うまく作動したとのことで安心しました。 別の方法として、SpecialCells(xlCellTypeVisible)を使う場合の例を書いておきます。 Dim dic 'As Scripting.Dictionary Set dic = CreateObject("Scripting.Dictionary") Dim c As Range For Each c In wb2.Sheets("sheet1").AutoFilter.Range.Columns(3).SpecialCells(xlCellTypeVisible) dic(c.Value) = c.Offset(, 2).Value Next Dim k As String For i = 4 To wb1.Sheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Row k = wb1.Sheets("sheet1").Cells(i, "C").Value If dic.Exists(k) Then wb1.Sheets("sheet1").Cells(i, "E") = Format(dic(k), "yyyy/mm/dd") End If Next i
doggyman10

2021/03/11 14:32

別方法についても丁寧に記載いただきありがとうございます。 勉強不足でScripting.Dictionaryについてそこまで詳しく存じ上げておらず申し訳ないのですが これは仮想のスペース(オブジェクト)にフィルター結果を格納しそこからif ループ処理を行なっているという認識で相違ないでしょうか? またまた気になった点があるので質問させてください。autofilterを用いてループ処理を行うのは処理方法としては余り好ましくないやり方になるのでしょうか?※現在はデータのキャパ的にそこまで影響はなさそうですが
jinoji

2021/03/11 15:11

Dictionaryはその名の通り辞書のようなもので、キーとアイテムの組み合わせで保持する配列です。 普通の配列が、0,1,2 という数字が添え字になっているの比べて、 文字列を添え字代わりに使えるので、 dic(名前)=電話番号 みたいな感じで値をセットしてことができます。 それを使って、まずwb2のC,E列の値を辞書に入れておいて、 wb1のC列の値で辞書を引き、その結果wb1のE列に入れていく感じです。 辞書を準備するときの処理が、 For Each c In wb2.Sheets("sheet1").AutoFilter.Range.Columns(3).SpecialCells(xlCellTypeVisible) つまりwb2ブックのSheet1のオートフィルタの範囲の3列目(C列)で表示されているセルのそれぞれを、cという変数として順番に処理するのですが、 dic(c.Value) = c.Offset(, 2).Value そのセルの値と、右に2つずらしたセル、つまりE列の値の組み合わせで格納していく、というわけです。 autofilterを用いてループ処理を行うのは、その時々のautofilterの状態によって処理結果が変わる(場合によってはエラーになったりする)ことをよしとするかどうかです。 人手とマクロの共同作業になる感じで、その人手が他人だったりすると、思っていたのと違う結果になることがあって、私自身は何となく避けています。
doggyman10

2021/03/13 14:31 編集

ご返信遅くなりすみません。 再三ご丁寧に説明いただきありがとうございます! Scripting.Dictionaryの理解はできましたが、 まだ今時点ではエラー出た時の対処で時間食われそうな気がするので 一旦Hidden判別の流れで処理を行おうと思います。 またAutofilterを用いてループ処理を行うことに対して、jinojiさんの仰る通りだと思います。 特にAutofilterを掛ける位置が1個だけでもズレるだけでうまくいかないということもあり得ると再認識させられました。 また私自身元々エクセル関数を通じて様々なデータを作ってきたため、ある程度処理手順(関数・手作業での)はある程度思い浮かベるのでそこの処理手順を元に今回はAutofilterとfor if文を採用しました。 Jinojiさん自身は避けているとのことですが差し支えなければAutofilterを使用せずに処理をループ処理を施す場合どういった処理になるのでしょうか。Autofilterを使用せずにfor if文で完結する形ですかね...? (僕のレベルではそこしか思いつきませんでした。)
jinoji

2021/03/14 09:21

おっしゃる通り、単純なループの中でIf文ですかね。 蛇足ながら、最近は、自分で作る処理では表データは出来るだけListObjectの形にしておくことが増えました。 Dim lo As ListObject, lr as ListRow Set lo = ws2.ListObjects(1) For Each lr In lo.ListRows '''' Next みたいな感じです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.36%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問