[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『変数を使ったフィルタ検索について』（風の試練）
Excelのマクロ使用して
変数を使ったフィルタ検索について
A列～DF列までのExcelの表があります
T列やAA列にも既に値が入っていて
選択した行の
T列セルをコピーしてT列で検索
更に
AA列セルをコピーしてAA列で検索
下記の例で言うなら、5行目を選択した状態でボタン(マクロ実行)を押すと
T列の行(T5セル)をコピーしてフィルタでその文字を検索（検索条件はT5セルの文字と等しいで検索）
そこから更にAA列も同様に
AA列の行(AA5セル)をコピーしてフィルタでその文字を検索（検索条件はAA5セルの文字と等しいで検索）
これを行うにはどのようなマクロにすれば良いでしょうか？
プラスαで選択した行に色付ける事は可能でしょうか？
下の式が過去に教えてもらった式と式を活用してみたのですができなくて...
.Row辺りでエラーになってしまって
Sub TEST()
Dim 検索文字T As String
With Selection
Range(Cells(.Row, "T"), Cells(.Row, "T").Offset(.Rows.Count - 1)).Select
End With
With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText ActiveCell.Value
.PutInClipboard
End With
ActiveCell.Copy
ActiveSheet.Range("A1").AutoFilter Field:=20, Criteria1:= 検索文字T
End Sub
＜ 使用 Excel：Microsoft365、使用 OS：Windows11 ＞
■１
一般的論として「Selection」とか「ActiveCell」に頼らないコードにした方がよいと思います。
■２
＞フィルタでその文字を検索
細かい話ですが【検索】と【抽出】は違います。
そして、オートフィルタは【抽出】する機能です。
■３
＞これを行うにはどのようなマクロにすれば良いでしょうか？
多分、提示されたコードをどうにかするより１から作った方が早いと思います。
1.オートフィルタを解除する 2.オートフィルタを設定する 3.Ｔ列のうち「T5セルの文字」と一致するものを、オートフィルタで【抽出】する 4.そのまま、AA列のうち「AA5セルの文字」と一致するものを、オートフィルタで【抽出】する 5.適当な行の最終行を調べて、項目行でなければデータが1件以上抽出されているということだから、着色（塗りつぶし設定）する
というマクロにすればよいでしょう。
いくつかの命令は、【マクロの記録】で調べることができますから、まずはそこからトライしてみてはいかがでしょうか。
■４
なお、おっしゃってることだけであれば、色付けはマクロを使わずとも条件付き書式で可能だと思います。
（もこな２） 2023/04/19(水) 18:53:50
Sub TEST()
Selection.AutoFilter
Selection.AutoFilter
Range("T5").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Range("A1").AutoFilter Field:=27, Criteria1:="AAB92"
Range("AA5").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Range("A1").AutoFilter Field:=27, Criteria1:="TA2"
Rows("10:10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
記録ではこうなるんですが、これだと、表の文字が毎回違うので使えないのですが…
そこで、変数で行う方が良いと思ったのですが
（風の試練） 2023/04/20(木) 15:18:24
実際に使ってる表がＴ列・ＡＡ列でフィルタしたかったので…
他には別に関数がなども入っていない普通の表になります。
（風の試練） 2023/04/20(木) 15:20:07
１つめのキーワード T5セル ２つめのキーワード AA5セル データは７行目以降（項目行は6行目）
だとしたら↓のような感じになるのではないでしょうか。
Sub TEST_改()
With ActiveSheet
.AutoFilterMode = False
.Range("A6:AA6").AutoFilter
.AutoFilter.Range.AutoFilter Field:=20, Criteria1:=.Range("T5").Value
.AutoFilter.Range.AutoFilter Field:=27, Criteria1:=.Range("AA5").Value
If .Cells(.Rows.Count, "T").End(xlUp).Row > 6 Then
Intersect(.AutoFilter.Range, .AutoFilter.Range.Offset(1)).Interior.Color = 65535
End If
End With
End Sub
変数は関係なくありませんか？
（もこな２） 2023/04/20(木) 16:04:35
T5やAA5は都度変わります。
今回は５行目を選択した状態での例だったので
6行目を選択した際は、T6やAA6になります。
また、色をつけたい箇所は、6行目を選択した際は6行目のみです。
（風の試練） 2023/04/21(金) 12:23:54
言ってることがよくわかりません。
５～６行、４～５で結構ですので【行・列を踏まえたレイアウト】を示して、【オートフィルタ】でどのような結果を導き出したいのか教えてください。
■６
上記とは別で、なんとなくですが↓のようなアプローチでよい気がしてきました
※事前に(A列から始まる表範囲に）オートフィルタが設定してあることが前提
オートフィルタが設定されている表範囲で
ダブルクリック・・・・・・その列を対象にダブルクリックしたセルと同じキーワードの行を抽出
右クリック・・・・・・・・オートフィルタが設定してある範囲の、その行を塗りつぶし
【例】
___A__ ___B__ ___C__
1 項目１ 項目２ 項目３
2 い Ａ
3 ろ Ａ
4 は Ｂ
5 い Ｂ
6 ろ Ｃ
7 は Ｃ
8 い Ａ
9 ろ Ａ
10 は Ｂ
↓ A4セルをダブルクリック(Ａ列が「は」であるものを抽出)
___A__ ___B__ ___C__
1 項目１ 項目２ 項目３
4 は Ｂ
7 は Ｃ
10 は Ｂ
↓ C4セルをダブルクリック(上記の状態からさらに、Ｃ列が「Ｂ」であるものを抽出)
___A__ ___B__ ___C__
1 項目１ 項目２ 項目３
4 は Ｂ
10 は Ｂ
↓ A4～C4セルのどれかを右クリック
___A__ ___B__ ___C__
1 項目１ 項目２ 項目３
4 は Ｂ ←この行を塗りつぶし
10 は Ｂ
上記のアプローチであれば↓で実現可能だとおもいます。
【シートモジュール】
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(AutoFilter.Range, Target) Is Nothing Then
Cancel = True
AutoFilter.Range.AutoFilter Field:=Target.Column, Criteria1:=Target.Value
End If
End Sub
'---------------------------------------------------------------------------------------
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(AutoFilter.Range, Target) Is Nothing Then
Cancel = True
Intersect(AutoFilter.Range, Target.EntireRow).Interior.Color = vbYellow
End If
End Sub
※研究用に提示しています。理解していただきたいので単純に丸パクリして完成！はご遠慮ください。 （内容を理解して結果として同じになったのであればＯＫです）
（もこな２） 2023/04/21(金) 16:06:11
式的には不格好ですが
下記でできました。
Sub TEST()
With Selection
Range(Cells(.Row, "A"), Cells(.Row, "A").Offset(.Rows.Count - 1)).Copy Range("D1")
End With
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Range("D1").Value
With Selection
Range(Cells(.Row, "B"), Cells(.Row, "B").Offset(.Rows.Count - 1)).Copy Range("E1")
End With
ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=Range("E1").Value
With Selection
Range(Cells(.Row, "A"), Cells(.Row, "B").Offset(.Rows.Count - 1)).Interior.Color = 65535
End With
End Sub
■１
一般的論として「Selection」とか「ActiveCell」に頼らないコードにした方がよいと思います。
このアドバイスがありましたが、どのようにすればいいのか分からないので
上の式で見本を頂けると助かります。
以上よろしくお願いします。
（風の試練） 2023/04/24(月) 12:56:18
01 Sub TEST()
02 With Selection
03 Range(Cells(.Row, "A"), Cells(.Row, "A").Offset(.Rows.Count - 1)).Copy Range("D1")
04 End With
05 ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Range("D1").Value
06 With Selection
07 Range(Cells(.Row, "B"), Cells(.Row, "B").Offset(.Rows.Count - 1)).Copy Range("E1")
08 End With
09 ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=Range("E1").Value
10 With Selection
11 Range(Cells(.Row, "A"), Cells(.Row, "B").Offset(.Rows.Count - 1)).Interior.Color = 65535
12 End With
13 End Sub
とりあえず確認。
★１ 「02」の【Selection】はどのセルを示しているのか教えてください。 関連して「03」の【Range(Cells(.Row, "A"), Cells(.Row, "A").Offset(.Rows.Count - 1))】はどのセルを想定しているのか教えてください。
★２ 「02」と「05」の【Selection】は同じものとの理解でよいでしょうか？ 関連して「03」の【Range(Cells(.Row, "B"), Cells(.Row, "B").Offset(.Rows.Count - 1))】はどのセルを想定しているのか教えてください。
■８
上記のコードで【D1:E1セル】以下に書き出すのはマストな処理なのですか？
Selectionが何行・何列あるのかわかりませんが、使っているのが1行目だけですから↓でも大体同じ結果になりそうな気がします。
Sub TEST_改二()
With ActiveSheet
.AutoFilterMode = False
.Range("A1:B1").AutoFilter
.AutoFilter.Range.AutoFilter Field:=1, Criteria1:=.Cells(ActiveCell.Row, 1).Value
.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=.Cells(ActiveCell.Row, 2).Value
Intersect(.AutoFilter.Range, ActiveCell.EntireRow).Interior.Color = 65535
End With
End Sub
■９
さらに、"選択"に拘りたいのであれば、↓のような方法もあると思います。
Sub 研究用()
'A列以降にオートフィルタが設定されていることが前提
'複数列対応（ただし、1列内に複数行の選択がある場合、一番下のものが採用される）
Stop 'ブレークポイントの代わり
Dim MyRNG As Range, tmpRNG As Range
With ActiveSheet.AutoFilter.Range
For Each MyRNG In Selection
.AutoFilter Field:=MyRNG.Column, Criteria1:=MyRNG.Value
Next MyRNG
Set tmpRNG = Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1))
If Not tmpRNG Is Nothing Then
tmpRNG.Interior.Color = 65535
Else
MsgBox "該当なし"
End If
End With
End Sub
このように意図して「Selection」とか「Active○○」を使うこともありますから、見本云々ではなく、まずどのようなことをしたいのかの整理をされてみてはいかがでしょうか？
（もこな２） 2023/04/24(月) 19:18:58
★１ 「02」の【Selection】はどのセルを示しているのか教えてください。 関連して「03」の【Range(Cells(.Row, "A"), Cells(.Row, "A").Offset(.Rows.Count - 1))】はどのセルを想定しているのか教えてください。 「02」の【Selection】は「03」です 「03」は、セルを選択した際のＡ列セルです
★２ 「02」と「05」の【Selection】は同じものとの理解でよいでしょうか？ 関連して「03」の【Range(Cells(.Row, "B"), Cells(.Row, "B").Offset(.Rows.Count - 1))】はどのセルを想定しているのか教えてください。 「05」に【Selection】はありませんが… 【Range(Cells(.Row, "B"), Cells(.Row, "B").Offset(.Rows.Count - 1))】は、セルを選択した際のＢ列セルです
02 With Selection
03 Range(Cells(.Row, "A"), Cells(.Row, "A").Offset(.Rows.Count - 1)).Copy Range("D1")
04 End With
これを一行で表す方法が分からないのでWith とEnd Withの間の１行に対してくくってます
「Sub TEST_改二」がまさに自分が行いたかった事です！
「研究用」もそうですね、今回の件に関しては色を付けたいのは選択している行のみだったので
「Sub TEST_改二」が一番合ってました！
ありがとうございます。
「研究用」は自分の知識ではまだ難しいので
「Sub TEST_改二」は本当に助かります！
（風の試練） 2023/04/25(火) 12:49:48
★１ 「02」の【Selection】はどのセルを示しているのか教えてください。【具体的なセル番地】でおしえてください ★２ 「02」と「06」の【Selection】は同じものとの理解でよいでしょうか？
たとえば、↓のようなときに【A4～B6セル】を選択しているということならば
___A__ ___B__ ... ___D__ ___E__
1 項目１ 項目２
2 い Ａ
3 ろ Ａ
4 は Ｂ
5 い Ｂ
6 ろ Ｃ
7 は Ｃ
8 い Ａ
9 ろ Ａ
10 は Ｂ
Sub 研究用01()
With Range("A4:B6")
Range(Cells(.Row, "A"), Cells(.Row, "A").Offset(.Rows.Count - 1)).Copy Range("D1")
Range(Cells(.Row, "B"), Cells(.Row, "B").Offset(.Rows.Count - 1)).Copy Range("E1")
End With
End Sub
↓「.Row」「.Rows.Count」を展開
Sub 研究用01()
With Range("A4:B6")
Range(Cells(4, "A"), Cells(4, "A").Offset(3 - 1)).Copy Range("D1")
Range(Cells(4, "B"), Cells(4, "B").Offset(3 - 1)).Copy Range("E1")
End With
End Sub
↓「Range」の中身を整理
Sub 研究用01()
With Range("A4:B6")
Range("A4", "A6")).Copy Range("D1")
Range("B4" ,"B6")).Copy Range("E1")
End With
End Sub
↓さらに「Range」の中身を整理
Sub 研究用01()
With Range("A4:B6")
Range("A4:A6")).Copy Range("D1")
Range("B4:B6")).Copy Range("E1")
End With
End Sub
↓踏まえると実質的にはこうなる
Sub 研究用01()
Range("A4:B6").Copy Range("D1")
End Sub
↓実行結果↓
___A__ ___B__ ... ___D__ ___E__
1 項目１ 項目２ は Ｂ
2 い Ａ い Ｂ
3 ろ Ａ ろ Ｃ
4 は Ｂ
5 い Ｂ
6 ろ Ｃ
7 は Ｃ
8 い Ａ
9 ろ Ａ
10 は Ｂ
これは、【A4:A6】や【F4:P6】なんかを"選択"していても同じ結果になるのは分かりますよね？
(わからない場合は、ステップ実行してご自身で確認してみてください）
■11
次に↓のような表で【A3】と【C7:C8】を選択したと考えてみてください
___A__ ___B__ ___C__
1 項目１ 項目２ 項目３
2 い あ Ａ
3 ろ い Ａ
4 は う Ｂ
5 い え Ｂ
6 ろ お Ｃ
7 は あ Ｃ
8 い い Ａ
9 ろ う Ａ
10 は え Ｂ
コードに直すと↓になります。
Sub 研究用02()
With Range("A3,C7:C8")
Range(Cells(.Row, "A"), Cells(.Row, "A").Offset(.Rows.Count - 1)).Copy Range("D1")
Range(Cells(.Row, "B"), Cells(.Row, "B").Offset(.Rows.Count - 1)).Copy Range("E1")
End With
End Sub
想像通りの結果になったでしょうか？
おそらく想像とは違う結果になったのではないでしょうか？
原因は「With Range("A3,C7:C8")」のような飛び飛びのセル範囲に対して「.Row」「.Rows.Count」を取り出そうとしても1つ目のセル範囲のものしか返ってこないことです。
すなわち以下のようなことになります。
Range("A3").Row → 3
Range("A3").Rows.Count → 1
したがって
Sub 研究用02()
Range(Cells(3, "A"), Cells(3, "A").Offset(1 - 1)).Copy Range("D1")
Range(Cells(3, "B"), Cells(3, "B").Offset(1 - 1)).Copy Range("E1")
End Sub
↓
Sub 研究用02()
Range(Cells(3, "A"), Cells(3, "A").Offset(0)).Copy Range("D1")
Range(Cells(3, "B"), Cells(3, "B").Offset(0)).Copy Range("E1")
End Sub
↓
Sub 研究用02()
Range(Cells(3, "A"), Cells(3, "A")).Copy Range("D1")
Range(Cells(3, "B"), Cells(3, "B")).Copy Range("E1")
End Sub
↓
Sub 研究用02()
Cells(3, "A").Copy Range("D1")
Cells(3, "B").Copy Range("E1")
End Sub
ということになるから３行目しか取り出されないわけです。
【どのセルを選択】しているときに【どのような結果】にしたいのかと聞いていたのは上記のようなことがあったためです。
■12
＞「Sub TEST_改二」がまさに自分が行いたかった事です！
研究してお分かりになったと思いますが、【Selection】から取り出しているのは「.Row」「.Rows.Count」だけですから、【列】は関係ありません。
したがって提示されたコードでやっていることは実質的には↓のようなことなりますよね
Sub TESTのうち実質的な部分()
With Selection
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=ActiveSheet.Cells(.Row, "A").Value
ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=ActiveSheet.Cells(.Row, "B").Value
Range(ActiveSheet.Cells(.Row, "A"), ActiveSheet.Cells(.Row, "B")).Interior.Color = 65535
End With
End Sub
このように「変数に入れる」「Ｄ列以降にコピペする」などの必要がないので、【見本云々ではなく、まずどのようなことをしたいのかの整理をされてみてはいかがでしょうか？】と申し上げた次第です。
■13
＞「研究用」は自分の知識ではまだ難しいので「Sub TEST_改二」は本当に助かります！
こちらの意図としては、どのコードも完成品のプレゼントではなく研究用や説明用として提示しているつもりです。
きちんと【ステップ実行】して研究のうえ、理解してから必要な部分のみご自身のコードに組み込んでください。
なお、【ステップ実行】という言葉を聞いたことがなければ↓を読んでみてください。
【ステップ実行】 https://www.239-programing.com/excel-vba/basic/basic023.html http://plus1excel.web.fc2.com/learning/l301/t405.html
また、↓も覚えておいて損はないと思います。
【ブレークポイント】 https://www.239-programing.com/excel-vba/basic/basic022.html https://www.tipsfound.com/vba/01010
【イミディエイトウィンドウ】 https://www.239-programing.com/excel-vba/basic/basic024.html https://excel-ubara.com/excelvba1/EXCELVBA486.html
【ローカルウィンドウ】 https://excel-ubara.com/excelvba4/EXCEL266.html http://excelvba.pc-users.net/fol8/8_2.html
（もこな２） 2023/04/26(水) 06:17:15
もちろん、完璧とは言えませんが
だいぶシコリが取れた気がします。
ただ、少しづつ謎がほぐれてきて思ったのですが
「Row」の使い方がいまいちよく分かりません。
ネットで行番号を取得するという事は分かったのですが
どういう風に使うのかがまだよく分かりません。
今回の下記については、なぜ選択したセルが選ばれる様になっているのか分かっていません。
その仕掛けは何なのでしょうか？
With Selection
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=ActiveSheet.Cells(.Row, "A").Value
ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=ActiveSheet.Cells(.Row, "B").Value
Range(ActiveSheet.Cells(.Row, "A"), ActiveSheet.Cells(.Row, "B")).Interior.Color = 65535
End With
（風の試練） 2023/04/27(木) 17:31:30
また、「With（ステートメント）」は理解できていますか？
（もこな２） 2023/04/27(木) 18:50:25
【イミディエイトウィンドウ】は、行いましたがいまいち意味が分かりませんでした。
Withは、理解しているつもりです。
（風の試練） 2023/05/10(水) 14:17:33
わからない部分がわかりませんが、以下をステップ実行したときに想像通りのものが表示されたということなら概ね理解できていると思います。そうでないなら「■11」を読み返してください。
Sub 実験()
Stop
Range("A3").Select
Debug.Print " 【Address】 " & Selection.Address(0, 0)
Debug.Print " 【Areas(1)】 " & Selection.Areas(1).Address(0, 0)
Debug.Print " 【Row】 " & Selection.Row
Debug.Print " 【Rows.Count】" & Selection.Rows.Count & vbLf
Stop
Range("A7:C7").Select
Debug.Print " 【Address】 " & Selection.Address(0, 0)
Debug.Print " 【Areas(1)】 " & Selection.Areas(1).Address(0, 0)
Debug.Print " 【Row】 " & Selection.Row
Debug.Print " 【Rows.Count】" & Selection.Rows.Count&; vbLf
Stop
Range("A3:C8,B1").Select
Debug.Print " 【Address】 " & Selection.Address(0, 0)
Debug.Print " 【Areas(1)】 " & Selection.Areas(1).Address(0, 0)
Debug.Print " 【Row】 " & Selection.Row
Debug.Print " 【Rows.Count】" & Selection.Rows.Count&; vbLf
Stop
Range("A3,C7:C8").Select
Debug.Print " 【Address】 " & Selection.Address(0, 0)
Debug.Print " 【Areas(1)】 " & Selection.Areas(1).Address(0, 0)
Debug.Print " 【Row】 " & Selection.Row
Debug.Print " 【Rows.Count】" & Selection.Rows.Count&; vbLf
Stop
Range("A9:B57,C7,G7,P1,O22").Select
Debug.Print " 【Address】 " & Selection.Address(0, 0)
Debug.Print " 【Areas(1)】 " & Selection.Areas(1).Address(0, 0)
Debug.Print " 【Row】 " & Selection.Row
Debug.Print " 【Rows.Count】" & Selection.Rows.Count&; vbLf
End Sub
■16
＞Withは、理解しているつもりです。
> 今回の下記については、なぜ選択したセルが選ばれる様になっているのか分かっていません。
> その仕掛けは何なのでしょうか？
> With Selection
> ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=ActiveSheet.Cells(.Row, "A").Value
> ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=ActiveSheet.Cells(.Row, "B").Value
> Range(ActiveSheet.Cells(.Row, "A"), ActiveSheet.Cells(.Row, "B")).Interior.Color = 65535
> End With
理解できてるなら↓ですよね
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=ActiveSheet.Cells(.Row, "A").Value
↓
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=ActiveSheet.Cells(Selection.Row, "A").Value
~~~~~~~~~~~~~
何がわかりませんか？
（もこな２） 2023/05/10(水) 16:28:55
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.